IRIS Data to Google Big Query - InterSystems Cloud SQL via Dataflow
Tags: analytics, cloud, tutorial, beginners
Published: 2023-08-28 15:08:26


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/97faalfuwxnkkdqw7bv7.png)
How to include IRIS Data into your Google Big Query Data Warehouse and in your Data Studio data explorations.  In this article we will be using Google Cloud Dataflow to connect to our InterSystems Cloud SQL Service  and build a job to persist the results of an IRIS query in Big Query on an interval.  If you were lucky enough to get access to Cloud SQL at Global Summit 2022 as mentioned in "InterSystems IRIS: What's New, What's Next", it makes the example a snap, but you can pull this off with any publicly or vpc accessible listener you have provisioned instead.

 

### <u>Prerequisites</u>

Provision InterSystems Cloud SQL for temporary use
 
  
You may need to make some phone calls or request access through the portal as I did to take the InterSystems Cloud SQL for a spin, but it is a very fast way to get up and running in seconds to carry out this demonstration or your IRIS Workloads.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/atuzrczwrfwhl1lp9wzi.png)
Inspecting your deployment, you can head over to the "External Connections" pane on the overview tab and build yourself a connection URL and retaining your credentials.  We went wide open for public access (0.0.0.0/0) to the listener and chose not to encrypt the listener either.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lip18rf6ufz75qburdfk.png)
From above, you'll to disseminate the following information...
<br /><strong>ConnectionURL: </strong></span><br /><code>jdbc:IRIS://k8s-c5ce7068-a4244044-265532e16d-2be47d3d6962f6cc.elb.us-east-1.amazonaws.com:1972/USER</code><br /><br /><strong>User/Pass:</strong><br /><code>SQLAdmin/Testing12!</code><br /><br /><strong>DriverClassName:</strong><br /><code>com.intersystems.jdbc.IRISDriver</code>

Setup Google Cloud Platform

Provision a GCP Project

<pre>gcloud projects create iris-2-datastudio --set-as-default</pre>
    
-Enable Big Query
-Enable DataFlow
-Enable Cloud Storage

    
    gcloud services enable  bigquery.googleapis.com
    gcloud services enable dataflow.googleapis.com
    gcloud services enable storage.googleapis.com
    

-Create a Cloud Storage Bucket

    
    gsutil mb gs://iris-2-datastudio</pre>
    

-Upload the <a href="https://intersystems-community.github.io/iris-driver-distribution/">latest connection driver</a> to the root of the bucket

    
    wget https://github.com/intersystems-community/iris-driver-distribution/raw/main/intersystems-jdbc-3.3.0.jar
gsutil cp intersystems-jdbc-3.3.0.jar gs://iris-2-datastudio
    

-Create a Big Query DataSet

    
    bq --location=us mk \
--dataset \
--description "sqlaas to big query" \
iris-2-datastudio:irisdata
    
-Create a Big Query Destination Table


Now this is where a super powerful advantage becomes somewhat of a nuisance to us.  Big Query can create tables on the fly if you supply a schema along with your payload, this is super great inside of pipelines and solutions, but in our case, we need to establish the table beforehand.  The process is straightforward as you can export a CSV from the IRIS Database quite easily with something like <a href="https://dbeaver.io/">DBeaver</a> etc, and when you have it, you can invoke the "create table" dialog underneath the dataset you created and use the CSV to create your table.  Make sure you have "auto generate schema" checked at the bottom of the dialog.<br /><br />
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/va56ynlxeh19iof7ehey.png) <span> This should complete your Google Cloud Platform setup, and we should be ready configure and run our Dataflow job.</span>
    </p>
  </div>
</div>

**<u>Google Dataflow Job</u>**  
If you followed the steps above you should have the following in your inventory to execute the job to read your InterSystems IRIS data and ingest it into Google Big Query using Google Dataflow.  
  
In the Google Cloud Console, head over to Dataflow and select "Create Job from Template"  
  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/22urys0w1x01jnf86ftu.png) <span> </span>  
This is a rather unnecessary/exhaustive illustration on how to instruct you to fill out a form with the generated pre-requisites, but it calls out the source of the components...


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hznrxi9su3lk3zmu8ulq.png) <span> </span>

<span> ... to round it out, make sure you expand the bottom section and supply your credentials for IRIS.</span>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p7ndg6mp5dwcnjq49sqz.png) <span> </span>

For the ones who found those screenshots offensive to your intelligence, here is the alternate route to go to keep you inside your comfort zone in the CLI to run the job:

<pre>gcloud dataflow jobs run iris-2-bq-dataflow \
--gcs-location gs://dataflow-templates-us-central1/latest/Jdbc_to_BigQuery \
--region us-central1 --num-workers 2 \
--staging-location gs://iris-2-datastudio/tmp \
--parameters connectionURL=jdbc:IRIS://<strong>k8s-c5ce7068-a4244044-265532e16d-2be47d3d6962f6cc.elb.us-east-1.amazonaws.com</strong>:1972/USER,driverClassName=<strong>com.intersystems.jdbc.IRISDriver</strong>,query=SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, SELF_REFERENCING_COLUMN_NAME, REFERENCE_GENERATION, USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME, IS_INSERTABLE_INTO, IS_TYPED, CLASSNAME, DESCRIPTION, OWNER, IS_SHARDED FROM INFORMATION_SCHEMA.TABLES;,outputTable=iris-2-datastudio:irisdata.dataflowtable,driverJars=gs://<strong>iris-2-datastudio/intersystems-jdbc-3.3.0.jar</strong>,bigQueryLoadingTemporaryDirectory=gs://iris-2-datastudio/input,username=SQLAdmin,password=<strong>Testing12!</strong></pre>

Once you have kicked off your job, you can bask in the glory a successful job run:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4p9otic7w27b37jqh8jh.png)<span> </span>  
<u><strong>Results</strong></u>  
  
Taking a look at our source data and query in InterSystems Cloud SQL...  
  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y2i5rpzvn5lede2xu80p.png) <span> </span>

... and then Inspecting the results in Big Query, it appears we do in fact, **have InterSystems IRIS Data in Big Query**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/01wsaz6j6ow48swnjple.png) <span> </span>  
Once we have the data in Big Query, it is trivial to include our IRIS data into Data Studio by selecting Big Query as the data source... this example below is missing some flair, but you can quickly see the IRIS data ready for manipulation in your Data Studio project.

  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t9yswad4uo0ajtd5bjt3.png) <span> </span>  
<span> </span>