Query Google Big Query from Cloud SQl

[Hajar Homayouni]

Hi all,

I have a large dataset in GBQ, and I want to query (select) a small part of it in Cloud SQL. Is there any way to do this?

[Rob]

ParaSQL's Hyper Connect will allow you to do this. 

[Hajar Homayouni]

Thank you Rob, could you please explain more how to do this?

[Adam (Cloud Platform Support)]

It's a bit unclear what you need to do. Are you asking how to import a subset of your BigQuery data into a Cloud SQL database?

[Hajar Homayouni]

Actually, I have a R application in which I want to read data from GBQ tables and write into CloudSql tables through a single query:

insert into [CloudSql table]

 select x from [GBQ]

I need something to connect CloudSql to GBQ (integrate them). Any idea would be really appreciated.

[Adam (Cloud Platform Support)]

As the SQL query is executed on the database this would require the use of federated tables. Unfortunately BigQuery only supports Google Cloud Storage and Google Drive as federated data sources, and MySQL only supports other MySQL databases.

Adding CloudSQL/MySQL as a federated data source for BigQuery sounds like a good feature to request on the issue tracker.

[Rob]

Hajar,

ParaSQL's Hyper Connect Engine is a MySQL compatible database engine that runs on Google Compute Engine. Unlike CloudSQL, it allows you to add linked servers sort of like a Microsoft SQL linked server (or federated tables in MySQL) but using any data source that has an ODBC driver available (JDBC coming soon). There are several commercial ODBC drivers available for BigQuery (for example, from Simba Technologies). Once the servers are connected, you can simply issue a command like:

insert into MySQLTable

select a,b from BigQueryTable where ...

You can also dynamically join across MySQL and BigQuery without copying the data first. So something like this is valid:

select t1.col1, t2.col3

from MySQLTable as t1

left join BigQueryTable on (BigQueryTable.col1 = MySQLTable.col7) as t2

where BigQueryTable.col12 between 45 and 56

order by BigQueryTable.col2, MySQLTable.col5

GROUP BY and aggregates (sum,min,avg,etc) also work. Nested sub-select syntax also works.

There WHERE clause is pushed down to BigQuery, so this works well so long as the amount of data coming back from the BigQuery part of the query isn't too huge.

On Friday, June 17, 2016 at 1:11:56 PM UTC-4, Hajar Homayouni wrote: