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 Couchbase Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Connecting to Couchbase

To connect to data, set the Server property to the hostname or IP address of the Couchbase server(s) you are authenticating to.

If your Couchbase server is configured to use SSL, you can enable it either by using an https URL for Server (like https://couchbase.server), or by setting the UseSSL property to True.

Couchbase Analytics

By default, the cmdlet connects to the N1QL Query service. In order to connect to the Couchbase Analytics service, you will also need to set the CouchbaseService property to Analytics.

Couchbase Cloud

Set the following to connect to Couchbase Cloud:

AuthScheme: Set this to Basic.
ConnectionMode: Set this to Cloud.
DNSServer: Set this to a DNS server. In most cases, this should be a public DNS service like 1.1.1.1 or 8.8.8.8.
SSLServerCert: Set this to the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected. Alternatively, set "*" to accept all certificates.

Authenticating to Couchbase

The cmdlet supports several forms of authentication. Couchbase Cloud only accepts Standard authentication, while Couchbase Server accepts Standard authentication, client certificates, and credentials files.

Standard Authentication

To authenticate with standard authentication, set the following:

AuthScheme: Set this to Basic.
User: The user authenticating to Couchbase.
Password: The password of the user authenticating to Couchbase.

Client Certificates

The cmdlet supports authenticating with client certificates when SSL is enabled. To use client certificate authentication, set the following properties:

AuthScheme: Set this to SSLCertificate.
SSLClientCertType: The type of client certificate set within SSLClientCert.
SSLClientCert: The client certificate in the format given by SSLClientCertType.
SSLClientCertPassword (optional): The password of the client certificate, if it is encrypted.
SSLClientCertSubject (optional): The subject of the client certificate, which, by default, is the first certificate found in the store. This is required if more than one certificate is available in the certificate store.

Credentials File

You can also authenticate using using a credentials file containing multiple logins. This is included for legacy use and is not recommended when connecting to a Couchbase Server that supports role-based authentication.

AuthScheme: Set this to CredentialsFile.
CredentialsFile: The path to the credentials file. Refer to Couchbase's documentation for more information on the format of this file.

Creating a Connection Object

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

$conn = Connect-Couchbase -User 'myusername' -Password 'mypassword' -Server 'http://couchbase40'

Retrieving Data

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

$results = Select-Couchbase -Connection $conn -Table "[MyBucket].[MyScope].[Customer]" -Columns @("Name, TotalDue") -Where "CustomerId='12345'"

The Invoke-Couchbase 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-Couchbase -Connection $conn -Table [MyBucket].[MyScope].[Customer] -Where "CustomerId = '12345'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\my[MyBucket].[MyScope].[Customer]Data.csv -NoTypeInformation

You will notice that we piped the results from Select-Couchbase 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-Couchbase -User 'myusername' -Password 'mypassword' -Server 'http://couchbase40'
PS C:\> $row = Select-Couchbase -Connection $conn -Table "[MyBucket].[MyScope].[Customer]" -Columns (Name, TotalDue) -Where "CustomerId = '12345'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  "[MyBucket].[MyScope].[Customer]",
  "Columns":  [

  ],
  "Name":  "MyName",
  "TotalDue":  "MyTotalDue"
}

Deleting Data

The following line deletes any records that match the criteria:

Select-Couchbase -Connection $conn -Table [MyBucket].[MyScope].[Customer] -Where "CustomerId = '12345'" | Remove-Couchbase

Modifying Data

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

Import-Csv -Path C:\My[MyBucket].[MyScope].[Customer]Updates.csv | %{
  $record = Select-Couchbase -Connection $conn -Table [MyBucket].[MyScope].[Customer] -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-Couchbase -Connection $conn -Table [MyBucket].[MyScope].[Customer] -Columns @("Name","TotalDue") -Values @($_.Name, $_.TotalDue) -Where "Id  = `'$_.Id`'"
  }else{
    Add-Couchbase -Connection $conn -Table [MyBucket].[MyScope].[Customer] -Columns @("Name","TotalDue") -Values @($_.Name, $_.TotalDue)
  }
}

Cmdlets for Couchbase