{"id":15421,"date":"2021-09-23T12:36:20","date_gmt":"2021-09-23T16:36:20","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=15421"},"modified":"2024-09-23T13:45:32","modified_gmt":"2024-09-23T17:45:32","slug":"connecting-bigquery-to-voracity","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/iri\/iri-workbench\/connecting-bigquery-to-voracity\/","title":{"rendered":"BigQuery Data Management via IRI Voracity"},"content":{"rendered":"

\"\"BigQuery is a managed, serverless data warehouse in the Google Cloud designed to enable scalable analysis over petabytes of data. <\/span>It is a relational database Platform as a Service (PaaS) which supports ANSI SQL queries. As such, it works with IRI software.<\/span><\/p>\n

Connecting the <\/span>Google BigQuery<\/span><\/a> RDB to <\/span>IRI Workbench<\/span><\/a> and the back-end <\/span>SortCL<\/span><\/a> processing program is simple, and allows for the movement and manipulation of its structured data through compatible <\/span>IRI products<\/span><\/a>. This means IRI CoSort, FieldShield, NextForm and RowGen, or the IRI Voracity data integration and management <\/span>platform<\/span><\/a> which includes them all.<\/span><\/p>\n

Connectivity follows the same paradigm as <\/span>all the other<\/span><\/a> relational databases which IRI <\/span>supports<\/span>. This means ODBC and JDBC driver downloading and installation, configuration (using and testing with your credentials), registration, and validation. Note that if you are using DarkShield, only the JDBC connection is required.<\/span><\/p>\n

As Workbench is built on Eclipse, it needs a JDBC connection to view BigQuery schema and parse the table metadata. And to pass data between BigQuery and the SortCL data manipulation engine, an ODBC driver is also needed. The final result could be this:<\/span><\/p>\n

\"\"<\/p>\n

Google has teamed up with Magnitude Simba to provide ODBC and JDBC <\/span>drivers<\/span><\/a> to connect to BigQuery. At the time of this writing however, its JDBC driver is missing key functions Workbench needs. To get around this, use the JDBC driver from <\/span>CData<\/span><\/a>.<\/span><\/p>\n

This article provides step by step instructions for IRI software to access BigQuery.<\/span><\/p>\n

Service Accounts in BigQuery<\/b><\/p>\n

BigQuery authorizes access to resources based on verified identity, which needs a user ID in the form of a service account and a key\/password. To create a verified identity, sign into BigQuery, go to Service Accounts under IAM & Admin and create an account:<\/span><\/p>\n

\"\"<\/p>\n

The first field creates the name of the service account, for my set up I called it iri-simba. The second field will automatically be filled in with a service account email address using the name you chose. The last field can be skipped. Click <\/span>Create and Continue.<\/span><\/i><\/p>\n

Now that a service account is created, we can move on to the type of permissions this account can have. Click on<\/span> Select a role<\/span><\/i> and look for BigQuery to add specific roles for the database.<\/span><\/p>\n

\"\"<\/p>\n

Hovering over each role will give you a quick description of what type of access this role will give to the service account; find a more detailed explanation <\/span>here<\/span><\/a>. This allows for greater control on giving specific users permission like the ability to be able to view tables, create queries or run as an administrator.\u00a0<\/span><\/p>\n

I chose the role of BigQuery User, which will allow this service account to view and manipulate tables. The \u201cGrant user access to this service account\u201d is skipped. Clicking <\/span>Done<\/span><\/i> takes you back to the service account main page where you can see the account:<\/span><\/p>\n

\"\"<\/p>\n

Moving on to the second portion, let’s create the key that will be associated with the new service account. In the Action field, click on <\/span>Manage Keys<\/span><\/i> to create the key for the service account — either by adding your own key or having it created for you.\u00a0<\/span><\/p>\n

If you have Google create your key, it will present you two key type options, JSON or P12. Choose the JSON type because this key will also be used for the JDBC driver which uses the JSON format.\u00a0<\/span><\/p>\n

When the JSON key is created, it will be downloaded onto the computer. You can place it wherever you like, but remember the path because this will be used in setting up the ODBC and JDBC driver.<\/span><\/p>\n

Now that the service account has been created and has a key that will act as the password, let’s move on to downloading the <\/span>ODBC connection<\/span><\/a> and setting it up.<\/span><\/p>\n

ODBC – Download and Configuration<\/b><\/p>\n

I am using a Windows operating system and choosing the 64-bit Windows version for compatibility with the CoSort V10.5 SortCL executable. Once you followed the instructions and accepted the license agreement for the Simba Installer, open the ODBC Data Source Administrator (64-bit) to configure the connection.<\/span><\/p>\n

\"\"<\/p>\n

Simply add and look for the driver named \u201cSimba ODBC Driver for Google BigQuery\u201d.<\/span><\/p>\n

\"\"<\/p>\n

With the driver selected, the setup page should look like this:<\/span><\/p>\n

\"\"<\/p>\n

Here the configuration is really simple, starting with the name for the data source. <\/span>
\n<\/span>I chose the name Google BigQuery but you can choose any name for your use case.\u00a0<\/span><\/p>\n

For authentication keep the default option Service Account and move down to email. Here you can copy and paste the service account email that was created earlier in this article.\u00a0<\/span><\/p>\n

The field below (Key File Path) uses the path to the JSON key file as the input. At the bottom where it states Catalog (Project) click the drop down menu. If everything is configured correctly, it should show the name of the project and node that contains the datasets and tables.\u00a0<\/span><\/p>\n

You can do the same for the Dataset option, click the drop down menu to select a specific dataset or leave this empty to see all the datasets in this project. Finally test the connection to ensure everything is working correctly.<\/span><\/p>\n

When ODBC is set up, we can configure the JDBC driver.<\/span><\/p>\n

JDBC – Download and Configuration<\/b><\/p>\n

Download the JDBC driver from <\/span>CData here<\/span><\/a>. Once the installation is complete there will be a folder called <\/span>GoogleBigQueryJDBCDriver<\/span><\/i> with a setup.jar inside.\u00a0<\/span><\/p>\n

The setup.jar will install all the files needed for the JDBC connection to work. It also contains a special jar to assist in creating the connection URL for the JDBC driver.<\/span><\/p>\n

After setup.jar completes the installation, we need to have the configurations in Workbench ready. In the Data Source Explorer (inside of Workbench), add a new connection by clicking on <\/span>New Connection Profile<\/span><\/i>.<\/span><\/p>\n

\"\"<\/p>\n

A pop will appear (like the picture below) and give several options on the type of connections that can be created. Select the Generic JDBC and give it a name such as BigQuery, this will make it easy to spot in the Data Source Explorer.<\/span><\/p>\n

\"\"<\/p>\n

The next page will direct you to set up the driver and provide the connection details. Click on <\/span>New Driver Definition<\/span><\/i> that looks like a compass with a green plus sign.<\/span><\/p>\n

\"\"<\/p>\n

The following page allows you to give a specific name to the driver if desired. Moving to the JAR List tab, this is where the required jars are added for the JDBC driver to function.<\/span><\/p>\n

If the default location was used when installing the files for the JDBC driver, it should be located in the Program Files folder with the name CData. Inside the lib folder there is a Jar file called <\/span>cdata.jdbc.googlebigquery.GoogleBigQueryDriver<\/span><\/i>, add that jar to the list and proceed to the Properties tab.<\/span><\/p>\n

*The default path is seen in the picture below if there is any trouble locating the jar file*<\/span><\/p>\n

\"\"<\/p>\n

In the Properties tab, we need to create a connection URL, give a name to the Database, and specify the Driver Class. Focusing first on creating the connection URL, in File Explorer locate the jar file that was just added and execute it.\u00a0<\/span><\/p>\n

This will help create the connection URL in the format that CData suggests. As seen in the picture below, there are properties on the left that need to be set in order to create the connection URL.<\/span><\/p>\n

\"\"<\/p>\n

CData has <\/span>documentation <\/span><\/a>on which properties need to be set depending on how the user chose to authenticate. Since we are authenticating with a Service Account the properties that need to be set are listed below.<\/span><\/p>\n