Query BigQuery information schema tables from SAS

[vigneswar17]

Hi Folks,

I'm trying to query BQ information schema tables from SAS. Sample code below,

proc sql;
CONNECT TO bigquery AS perm (project='dwh-1-svc-prj-1' Bulkunload=no schema='INFORMATION_SCHEMA'
cred_path='/home/cred.json' Bulkunload=no SCANSTRINGS=yes read_mode=storage);
execute(create table &_tt..testinfo as
select * from TABLES limit 100
) by perm;
quit;

28 proc sql;
29 CONNECT TO bigquery AS perm (project='dwh-1-svc-prj-1' Bulkunload=no schema='INFORMATION_SCHEMA'
30 cred_path=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Bulkunload=no SCANSTRINGS=yes read_mode=storage);
31 execute(create table &_tt..testinfo as
32 select * from TABLES limit 100
33 ) by perm;
ERROR: CLI execute error: Base table or view not found

Basically I'm trying to create a table by doing select * information_schema.tables; But somehow it is not able find the "TABLES" under the information schema. Can anyone help on this?

Any leads on this are much appreciated.

Thanks,

Vigneswar Jeyaraj

[kolban · Accepted solution]

If we read in depth the documentation on INFORMATION_SCHEMA.TABLES found here ... we find that the name of the view is called "INFORMATION_SCHEMA.TABLES" and that you should be providing a dataset (schema).  For example, a SQL query would look like:

SELECT * FROM <Your dataset>.INFORMATION_SCHEMA.TABLES

I think you have possible confused yourself thinking that "INFORMATION_SCHEMA" is the name of the schema you want to supply ... rather "INFORMATION_SCHEMA.TABLES" is the name, of the view.

[vigneswar17]

Thank you @kolban Much appreciated for pointing that... it worked now 🙂