Establishing a Connection

With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our HarperDB Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Installing and Connecting

If you have PSGet, installing the cmdlets can be accomplished from the PowerShell Gallery with the following command. You can also obtain a setup from the CData site.

Install-Module HarperDBCmdlets

The following line is then added to your profile, loading the cmdlets on the next session:

Import-Module HarperDBCmdlets;

You can then use the Connect-HarperDB cmdlet to create a connection object that can be passed to other cmdlets:

$conn = Connect-HarperDB -Server '127.0.0.1' -Port 9925

Connecting to HarperDB

Set the Server connection property to the hostname or IP address of your HarperDB instance. Set UseSSL to secure connections with TLS/SSL.

Authenticating to HarperDB

The cmdlet supports Basic authentication and token authentication. To authenticate, select the desired authentication method in the AuthScheme property, and set the necessary credentials described below.

Basic

Set AuthScheme to Basic. Set User and Password to a HarperDB user and password.

Token

Set AuthScheme to Token. The cmdlet supports Token authentication through the following schemes:

Driver managed: Set User and Password to a HarperDB user and password. The driver handles obtaining and refreshing OperationToken and RefreshToken. Encrypted token credentials are stored at CredentialsLocation.
User managed: Directly specify OperationToken and RefreshToken. The driver does not attempt to refresh or reacquire token credentials after the supplied tokens expire. The supplied tokens are located at CredentialsLocation but do not persist across connections.

The cmdlet also offers the following properties to govern token management in the Driver managed scheme:

OperationTokenTimeout: this governs how long the driver attempts to use a supplied OperationToken before refreshing it. It should match the setting for operation_token lifetimes in HarperDB. It defaults to the default operation_token lifetime for HarperDB.
RefreshTokenTimeout: this governs how long the driver attempts to use a supplied RefreshToken before refreshing it. It should match the setting for refresh_token lifetimes in HarperDB. It defaults to the default refresh_token lifetime for HarperDB.

Retrieving Data

The Select-HarperDB cmdlet provides a native PowerShell interface for retrieving data:

$results = Select-HarperDB -Connection $conn -Table "[HarperDB].[cstest].Cutomers" -Columns @("City, CompanyName") -Where "Country='US'"

The Invoke-HarperDB cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file:

Select-HarperDB -Connection $conn -Table [HarperDB].[cstest].Cutomers -Where "Country = 'US'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\my[HarperDB].[cstest].CutomersData.csv -NoTypeInformation

You will notice that we piped the results from Select-HarperDB 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 object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON:

 
PS C:\> $conn  = Connect-HarperDB -Server '127.0.0.1' -Port 9925
PS C:\> $row = Select-HarperDB -Connection $conn -Table "[HarperDB].[cstest].Cutomers" -Columns (City, CompanyName) -Where "Country = 'US'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  "[HarperDB].[cstest].Cutomers",
  "Columns":  [

  ],
  "City":  "MyCity",
  "CompanyName":  "MyCompanyName"
}

Deleting Data

The following line deletes any records that match the criteria:

Select-HarperDB -Connection $conn -Table [HarperDB].[cstest].Cutomers -Where "Country = 'US'" | Remove-HarperDB

Modifying Data

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

Import-Csv -Path C:\My[HarperDB].[cstest].CutomersUpdates.csv | %{
  $record = Select-HarperDB -Connection $conn -Table [HarperDB].[cstest].Cutomers -Where ("_id = `'"+$_._id+"`'")
  if($record){
    Update-HarperDB -Connection $conn -Table [HarperDB].[cstest].Cutomers -Columns @("City","CompanyName") -Values @($_.City, $_.CompanyName) -Where "_id  = `'$_._id`'"
  }else{
    Add-HarperDB -Connection $conn -Table [HarperDB].[cstest].Cutomers -Columns @("City","CompanyName") -Values @($_.City, $_.CompanyName)
  }
}

Cmdlets for HarperDB