PowerShell Scripting to Replicate Google Cloud Storage Data to MySQL

The CData Cmdlets for Google Cloud Storage offer live access to Google Cloud Storage data from within PowerShell. Using PowerShell scripts, you can easily automate regular tasks like data replication. This article will walk through using the CData Cmdlets for Google Cloud Storage and the CData Cmdlets for MySQL in PowerShell to replicate Google Cloud Storage data to a MySQL database.

After obtaining the needed connection properties, accessing Google Cloud Storage data in PowerShell and preparing for replication consists of four basic steps.

Authenticate with a User Account

You can connect without setting any connection properties for your user credentials. After setting InitiateOAuth to GETANDREFRESH, you are ready to connect.

When you connect, the Google Cloud Storage OAuth endpoint opens in your default browser. Log in and grant permissions, then the OAuth process completes

Authenticate with a Service Account

Service accounts have silent authentication, without user authentication in the browser. You can also use a service account to delegate enterprise-wide access scopes.

You need to create an OAuth application in this flow. See the Help documentation for more information. After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to "PFXFILE".
• OAuthJWTCert: Set this to the path to the .p12 file you generated.
• OAuthJWTCertPassword: Set this to the password of the .p12 file.
• OAuthJWTCertSubject: Set this to "*" to pick the first certificate in the certificate store.
• OAuthJWTIssuer: In the service accounts section, click Manage Service Accounts and set this field to the email address displayed in the service account Id field.
• OAuthJWTSubject: Set this to your enterprise Id if your subject type is set to "enterprise" or your app user Id if your subject type is set to "user".
• ProjectId: Set this to the Id of the project you want to connect to.

The OAuth flow for a service account then completes.

Collecting Google Cloud Storage Data

1. Install the module:

   Install-Module GoogleCloudStorageCmdlets

2. Connect to Google Cloud Storage:

   $googlecloudstorage = Connect-GoogleCloudStorage -ProjectId $ProjectId

3. Retrieve the data from a specific resource:

   $data = Select-GoogleCloudStorage -Connection $googlecloudstorage -Table "Buckets"

   You can also use the Invoke-GoogleCloudStorage cmdlet to execute pure SQL-92 statements:

   $data = Invoke-GoogleCloudStorage -Connection $googlecloudstorage -Query 'SELECT * FROM Buckets WHERE Name = @Name' -Params @{'@Name'='TestBucket'}

4. Save a list of the column names from the returned data.

   $columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name

Inserting Google Cloud Storage Data into the MySQL Database

With the data and column names collected, you are ready to replicate the data into a MySQL database.

1. Install the module:

   Install-Module MySQLCmdlets

2. Connect to MySQL, using the server address and port of the MySQL server, valid user credentials, and a specific database with the table in which the data will be replicated:

   $mysql = Connect-MySQL -User $User -Password $Password -Database $Database -Server $Server -Port $Port

3. Loop through the Google Cloud Storage data, store the values, and use the Add-MySQL cmdlet to insert the data into the MySQL database, one row at a time. In this example, the table will need to have the same name as the Google Cloud Storage resource (Buckets) and to exist in the database.

   $data | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "Buckets" -Columns $columns -Values $values }

You have now replicated your Google Cloud Storage data to a MySQL database. This gives you freedom to work with Google Cloud Storage data in the same way that you work with other MySQL tables, whether that is performing analytics, building reports, or other business functions.

Notes

• Once you have connected to Google Cloud Storage and MySQL in PowerShell, you can pipe command results to perform the replication in a single line:

  Select-GoogleCloudStorage -Connection $googlecloudstorage -Table "Buckets" | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "Buckets" -Columns $columns -Values $values }

• If you wish to replicate the Google Cloud Storage data to another database using another PowerShell module, you will want to exclude the Columns, Connection, and Table columns from the data returned by the Select-GoogleCloudStorage cmdlet since those columns are used to help pipe data from one CData cmdlet to another:

  $columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name | ? {$_ -NotIn @('Columns','Connection','Table')}