I’m trying to run a named query against Google Big Query (BQ).
Formatting the tablename is causing a problem.
My tables in BQ are organized thusly: Project.dataset.table
If I run a query against just the tablename i.e.( INSERT INTO tablename …) I get a nice error from Google telling me to include the dataset name as well.
When I include it ( INSERT INTO datasetname.tablename …) I get a 404 error Projectname:datasetname.tablename does not exist. notice that semi colon.
When I run a query in BQ’s online console I use the full “path” (INSERT INTO pname.dname.tname …).
On a whim I tried this as the format for my named query.
I did have some success but switched back to a testDB with a different table scheme.
On switching back to the BQ DB I can’t seem to get this query to work.
I’ve re-tried the three combinations above and haven’t been successful.
Anyone out there use BQ with there projects?
EDIT:
It was a capitalization error.
I ended up on (INSERT INTO pname.dname.tname …) working successfully.
Hello Ian,
I am trying to have connection to Big Query from Ignition instance. I see you are doing the same in here. Can you explain how you connected Big Query to your Ignition instance?
Before you connect your gateway to your BigQuery instance you'll need to set up a translater and driver.
This is done in config/Database/Database/Drivers.
You can find the jdbc driver and a guide for installation on googles website.
Now you can set up the DB connection as you would any other. The hardest part is likely going to be building the connection string, I needed help myself to get it right. It'll contain info that you want to keep private so I recommend handling that part internally.
A final note; I wouldn't recommend using BQ with the built in tag historian system. BigQuery doesn't work the same way a normal DB does and is more of a data lake. Our ignition system only writes to it, we historise data for ignition's use in a more typical DB. If you are just looking for a cloud based historian, I'd suggest using the SQL module in GCP (if you are tied into GCP already).
Thank you for the information Ian.
My use case will be just reading the data from BigQuery and not write tag values from Ignition. For the historian purpose, I will be having another database instance.