SAS BigQuery integration

[K-I]

Hello,

I have a question about SAS access to GCP BigQuery.
We have some data on GCP BigQuery that we want to access using SAS.
We have SAS on-prem version SAS 9.4.
I saw that SAS has a product SAS/ACCESS Interface to Google BigQuery which can be used for achieving this task.
Some questions:

• Any other option how data can be retrieved from BQ not using SAS/ACCESS Interface to Google BigQuery but using SAS. I am not refering to any intermediate service (python for example) which can be used to extract data from BQ.
• If I create a query and run it. How does SAS behaves? Does it enables BQ engine to process data or does it use SAS engine to process it.
• I heard that it is quite expensive to move data from GCP to on-prem server (SAS server). Is it a myth or truth?
• How does SAS deals with structs or arrays in BQ?

Thanks in advance

[Patrick]

This feels very much like a question for you @LinusH 

[LinusH · Accepted solution]

Thanks for the confidence in me @Patrick  🙂

I have no experience of accessing BigQuery though, but I try to rely on overall knowledge of SAS/ACCESS engines.

• Any other option how data can be retrieved from BQ not using SAS/ACCESS Interface to Google BigQuery but using SAS. I am not referring to any intermediate service (python for example) which can be used to extract data from BQ.
• As @Ksharp  mentions there is ODBC, but that is also an ACCESS engine. Question is why you don't want to use an ACCESS engine? Nor a "intermediate servce", not sur ewhat other options there are... You can access the underlying s3 buckets maybe, but then you won't utilize BQ compute.
• If I create a query and run it. How does SAS behave? Does it enable BQ engine to process data or does it use SAS engine to process it.
• In the ACCESS use case: generally speaking, if SAS can translate your query to SQL that the source "understands", it will pass it through. See the documentation for the specific ACCESS engine what limitations there are. This concept is usually called implicit pass through.
• You can also write queries using DB specific syntax ("explicit pass through). Then the whole query will execute in the source DB. The result can either be stored in the DB (execute (create table... ) by bigquery), or returned to SAS (select * from connection to bigquery(select ...)).
• I heard that it is quite expensive to move data from GCP to on-prem server (SAS server). Is it a myth or truth?
• In the monetary aspect, I don't know. You'll probably need to check with your agreements, estimate how much will travel etc.
• In a ACCESS scenario you usually want to limit data transfer between platforms as much as possible. So setting up guidelines/structures etc could be a good idea.
• How does SAS deals with structs or arrays in BQ?
• Well, SAS doesn't. So, you probably need to write explicit SQL and/or create views that unravels structures like arrays, Json, XML and similar data types.

[Ksharp]

Can you find ODBC driver for GCP BigQuery? You can connect it by ODBC .