Pipe AlloyDB Data to CSV in PowerShell

The CData Cmdlets Module for AlloyDB is a standard PowerShell module offering straightforward integration with AlloyDB. Below, you will find examples of using our AlloyDB Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your AlloyDB Data

The following connection properties are usually required in order to connect to AlloyDB.

• Server: The host name or IP of the server hosting the AlloyDB database.
• User: The user which will be used to authenticate with the AlloyDB server.
• Password: The password which will be used to authenticate with the AlloyDB server.

You can also optionally set the following:

• Database: The database to connect to when connecting to the AlloyDB Server. If this is not set, the user's default database will be used.
• Port: The port of the server hosting the AlloyDB database. This property is set to 5432 by default.

Authenticating with Standard Authentication

Standard authentication (using the user/password combination supplied earlier) is the default form of authentication.

No further action is required to leverage Standard Authentication to connect.

Authenticating with pg_hba.conf Auth Schemes

There are additional methods of authentication available which must be enabled in the pg_hba.conf file on the AlloyDB server.

Find instructions about authentication setup on the AlloyDB Server here.

Authenticating with MD5 Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.

Authenticating with SASL Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.

Authenticating with Kerberos

The authentication with Kerberos is initiated by AlloyDB Server when the ∏ is trying to connect to it. You should set up Kerberos on the AlloyDB Server to activate this authentication method. Once you have Kerberos authentication set up on the AlloyDB Server, see the Kerberos section of the help documentation for details on how to authenticate with Kerberos.

$conn = Connect-AlloyDB  -User "$User" -Password "$Password" -Database "$Database" -Server "$Server" -Port "$Port"

Selecting Data

Follow the steps below to retrieve data from the Orders table and pipe the result into to a CSV file:

Select-AlloyDB -Connection $conn -Table Orders | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myOrdersData.csv -NoTypeInformation

You will notice that we piped the results from Select-AlloyDB into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

Deleting Data

The following line deletes any records that match the criteria:

Select-AlloyDB -Connection $conn -Table Orders -Where "ShipCountry = USA" | Remove-AlloyDB

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into AlloyDB, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyOrdersUpdates.csv | %{
  $record = Select-AlloyDB -Connection $AlloyDB -Table Orders -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-AlloyDB -Connection $alloydb -Table Orders -Columns ("ShipName","ShipCity") -Values ($_.ShipName, $_.ShipCity) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-AlloyDB -Connection $alloydb -Table Orders -Columns ("ShipName","ShipCity") -Values ($_.ShipName, $_.ShipCity)
  }
}

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!