ERROR: CLI execute error: Error: googleapi: Error 400: Not found: Dataset XXXX

[vigneswar17]

Hi Folks,

I'm trying to run multi-statement query from SAS but it gives me the below error,

proc sql;
CONNECT TO bigquery AS perm (project='XXXX' Bulkunload=no schema='pnaus4r' cred_path='/home/cred.json');
execute(DECLARE session_id STRING DEFAULT CAST(UNIX_SECONDS(CURRENT_TIMESTAMP) as string);
DECLARE dataset_name STRING DEFAULT "ke_228_202307110007550.temp_dataset" ;
DECLARE table_name_1 STRING;
SET table_name_1 = "john_doe_raw";
EXECUTE IMMEDIATE "create table " || dataset_name || table_name_1 || "_" || session_id || " as Select 1 as my_column; " )by perm;

29 proc sql;
30 CONNECT TO bigquery AS perm (project='XXXX' Bulkunload=no schema='pnaus4r'
30 ! cred_path=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);
31 execute(DECLARE session_id STRING DEFAULT CAST(UNIX_SECONDS(CURRENT_TIMESTAMP) as string);
32 DECLARE dataset_name STRING DEFAULT "ke_228_202307110007550.temp_dataset" ;
33 DECLARE table_name_1 STRING;
34 SET table_name_1 = "john";
35 EXECUTE IMMEDIATE "create table " || dataset_name || table_name_1 || "_" || session_id || " as Select 1 as my_column; "
35 ! )by perm;

ERROR: CLI execute error: Error: googleapi: Error 400: Not found: Dataset XXXX:ke_228_202307110007550 was

not found in location US at [1:1], invalidQuery

Though it is

Can anyone provide inputs on this?

Thanks,

Vigneswar

[ms4446 · Accepted solution]

The error message you're encountering, Error 400: Not found: Dataset XXXX:ke_228_202307110007550 was not found in location US at [1:1], invalidQuery, indicates that the specified dataset ke_228_202307110007550 in your Google Cloud BigQuery project (XXXX) cannot be found in the location US. This could be due to several reasons:

1. Incorrect Dataset Name:

   • Double-check for typos: Ensure the dataset name ke_228_202307110007550 is spelled correctly and matches the exact name of an existing dataset in your BigQuery project. Pay close attention to capitalization.
   • Verify the project ID: The project ID ('XXXX') specified in the CONNECT TO statement should match the project where the dataset resides.

2. Dataset Not Created:

   • If you intend for the query to dynamically create a new dataset, you need to issue a CREATE DATASET command before your CREATE TABLE statements. It seems like you might be trying to do this, so check if the dataset creation step is successful.

3. Permissions Issues:

   • Ensure the service account associated with your SAS connection has the necessary permissions (BigQuery Data Editor or BigQuery Job User role) to work with datasets in the specified project.

Debugging & Best Practices:

• Isolate the Issue: Try executing the CREATE DATASET part of the query (if intended) separately to see if it executes without errors. Then test the CREATE TABLE statement. This can help pinpoint the failing part.
• Logging: Add logging statements in your SAS code to print out variables like session_id, dataset_name, and table_name_1 just before the EXECUTE IMMEDIATE statements. This will help you verify if the values are being constructed correctly.
• Clear Naming Conventions: Establish clear naming conventions for datasets and tables to avoid confusion and potential conflicts.
• Test Incrementally: When working with multi-statement queries, build and test them incrementally, adding one statement at a time. This helps isolate issues more easily.

Here's a revised version of your dynamic SQL statement with added clarity for the concatenation and syntax:

proc sql;
CONNECT TO bigquery AS perm (project='XXXX' Bulkunload=no schema='pnaus4r' cred_path='/home/cred.json');

/* If the dataset needs to be created */
execute(
  DECLARE session_id STRING DEFAULT CAST(UNIX_SECONDS(CURRENT_TIMESTAMP) as string);
  DECLARE dataset_name STRING DEFAULT "ke_228_202307110007550";
  EXECUTE IMMEDIATE "CREATE DATASET IF NOT EXISTS " || dataset_name 
) by perm;

/* ... then your CREATE TABLE statements */

/* ... */

quit;

[mars124]

I am looking to resolve a current similar error. It seems to be that statements using EXECUTE IMMEDIATE will have a job location set to US by default. Even if you already have created a prior data set that is not in the US, the execution will look for the data set in the US location, hence the error.