Automate HubDB Integration Tasks from PowerShell

The CData Cmdlets for HubDB are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to HubDB.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to HubDB, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete HubDB data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for HubDB. To access HubDB data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for HubDB.

Once you have acquired the necessary connection properties, accessing HubDB data in PowerShell can be enabled in three steps.

There are two authentication methods available for connecting to HubDB data source: OAuth Authentication with a public HubSpot application and authentication with a Private application token.

Using a Custom OAuth App

AuthScheme must be set to "OAuth" in all OAuth flows. Be sure to review the Help documentation for the required connection properties for you specific authentication needs (desktop applications, web applications, and headless machines).

Follow the steps below to register an application and obtain the OAuth client credentials:

1. Log into your HubSpot app developer account.
   • Note that it must be an app developer account. Standard HubSpot accounts cannot create public apps.
2. On the developer account home page, click the Apps tab.
3. Click Create app.
4. On the App info tab, enter and optionally modify values that are displayed to users when they connect. These values include the public application name, application logo, and a description of the application.
5. On the Auth tab, supply a callback URL in the "Redirect URLs" box.
   • If you're creating a desktop application, set this to a locally accessible URL like http://localhost:33333.
   • If you are creating a Web application, set this to a trusted URL where you want users to be redirected to when they authorize your application.
6. Click Create App. HubSpot then generates the application, along with its associated credentials.
7. On the Auth tab, note the Client ID and Client secret. You will use these later to configure the driver.

8. Under Scopes, select any scopes you need for your application's intended functionality.

   A minimum of the following scopes is required to access tables:

   • hubdb
   • oauth
   • crm.objects.owners.read
9. Click Save changes.
10. Install the application into a production portal with access to the features that are required by the integration.
    • Under "Install URL (OAuth)", click Copy full URL to copy the installation URL for your application.
    • Navigate to the copied link in your browser. Select a standard account in which to install the application.
    • Click Connect app. You can close the resulting tab.

Using a Private App

To connect using a HubSpot private application token, set the AuthScheme property to "PrivateApp."

You can generate a private application token by following the steps below:

1. In your HubDB account, click the settings icon (the gear) in the main navigation bar.
2. In the left sidebar menu, navigate to Integrations > Private Apps.
3. Click Create private app.
4. On the Basic Info tab, configure the details of your application (name, logo, and description).
5. On the Scopes tab, select Read or Write for each scope you want your private application to be able to access.
6. A minimum of hubdb and crm.objects.owners.read is required to access tables.
7. After you are done configuring your application, click Create app in the top right.
8. Review the info about your application's access token, click Continue creating, and then Show token.
9. Click Copy to copy the private application token.

To connect, set PrivateAppToken to the private application token you retrieved.

PowerShell

1. Install the module:

   Install-Module HubDBCmdlets

2. Connect:

   $hubdb = Connect-HubDB -AuthScheme "$AuthScheme" -OAuthClientID "$OAuthClientID" -OAuthClientSecret "$OAuthClientSecret" -CallbackURL "$CallbackURL" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $id = "1"
   $northwindproducts = Select-HubDB -Connection $hubdb -Table "NorthwindProducts" -Where "Id = `'$Id`'"
   $northwindproducts
   

   You can also use the Invoke-HubDB cmdlet to execute SQL commands:

   $northwindproducts = Invoke-HubDB -Connection $hubdb -Query 'SELECT * FROM NorthwindProducts WHERE Id = @Id' -Params @{'@Id'='1'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for HubDB\lib\System.Data.CData.HubDB.dll")
    

2. Connect to HubDB:

    
   $conn= New-Object System.Data.CData.HubDB.HubDBConnection("AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the HubDBDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT PartitionKey, Name from NorthwindProducts"
   
   $da= New-Object System.Data.CData.HubDB.HubDBDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.partitionkey $_.name
   }
    

Update HubDB Data

PowerShell

Update-HubDB -Connection $HubDB -Columns @('PartitionKey','Name') -Values @('MyPartitionKey', 'MyName') -Table NorthwindProducts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.HubDB.HubDBCommand("UPDATE NorthwindProducts SET Id='1' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.HubDB.HubDBParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert HubDB Data

PowerShell

Add-HubDB -Connection $HubDB -Table NorthwindProducts -Columns @("PartitionKey", "Name") -Values @("MyPartitionKey", "MyName") 

ADO.NET

$cmd = New-Object System.Data.CData.HubDB.HubDBCommand("INSERT INTO NorthwindProducts (Id) VALUES (@myId)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.HubDB.HubDBParameter("@myId","1")))
$cmd.ExecuteNonQuery()

Delete HubDB Data

PowerShell

Remove-HubDB -Connection $HubDB -Table "NorthwindProducts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.HubDB.HubDBCommand("DELETE FROM NorthwindProducts WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.HubDB.HubDBParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject