Name format to create Dataset.

[appflowpersiste]

I Would like to know that, does Dataset name accepts only numbers or alphanumerical or alphabets?

Case 1: only with number

I have created the dataset name with numbers only, when I am calling that dataset query through API it is giving me error.

Case 2: with alphanumerical

I have created the dataset name with alphanumerical, and it is fetching data from that dataset table. 

Case 3: with alphanumerical but start with number

I have created the dataset name with the alphanumerical and that alphanumerical start with numbers, when I am calling that dataset query through API it is giving me error.

Please find below request and response 

Request:

https://bigquery.googleapis.com/bigquery/v2/projects/p***al-tr**il-35****9/queries

Query :

1) SELECT * from 9gsh693.ga_sessions_202402032
2) SELECT * from 9897693.ga_sessions_202402002
Both the query giving same error response
Response :

{
    "error": {
        "code": 400,
        "message": "Encountered \" \"FROM\" \"from \"\" at line 1, column 10.\nWas expecting:\n    <EOF> \n    ",
        "errors": [
            {
                "message": "Encountered \" \"FROM\" \"from \"\" at line 1, column 10.\nWas expecting:\n    <EOF> \n    ",
                "domain": "global",
                "reason": "invalidQuery",
                "location": "q",
                "locationType": "parameter"
            }
        ],
        "status": "INVALID_ARGUMENT"
    }
}

[ms4446]

BigQuery enforces specific rules for dataset names to ensure compatibility and avoid parsing errors:

• Case Sensitivity: Dataset names are case-sensitive, meaning DatasetName and datasetname would be considered different datasets.

• Allowed Characters: Names can include lowercase letters (a-z), uppercase letters (A-Z), numbers (0-9), and underscores (_).

• Length: Dataset names can be up to 1024 characters in length.

• Starting Character: A dataset name must start with a letter (a-z, A-Z) or an underscore (_). Names starting with numbers or consisting solely of numbers do not comply with BigQuery's naming conventions.

• Case 1 (Only Numbers): Your dataset name consists solely of numbers, which is likely causing the error because BigQuery expects an identifier to not start with a numeric character unless it is enclosed in backticks.

• Case 3 (Alphanumeric Starting with a Number): This case also violates BigQuery's naming rules for the same reason as Case 1. The initial numeric character leads to a parsing error unless the name is enclosed in backticks.

Correcting Queries:

To address these issues, you should enclose your dataset names in backticks. This approach is necessary for dataset names that start with a number or otherwise do not conform to the identifier rules. Here's how to adjust your queries:

• For datasets starting with numbers or consisting solely of numbers:

SELECT * FROM `9gsh693`.ga_sessions_202402032

SELECT * FROM `9897693`.ga_sessions_202402002

Enclosing the dataset name in backticks (`) tells BigQuery to interpret the name literally, bypassing the default parsing that leads to errors.

Error Message:

The error message you encountered ("Encountered " "FROM" "from "" at line 1, column 10") indicates a syntax parsing issue. BigQuery did not recognize the part of your query before "FROM" as a valid dataset identifier due to the numeric start. By using backticks around the dataset names, you inform BigQuery to treat these sequences as literal identifiers, resolving the syntax issue and allowing your query to proceed.

Your understanding of BigQuery's dataset naming conventions is correct, and the solution to use backticks for dataset names that start with a number or contain special characters is appropriate. This practice is crucial for ensuring your SQL queries are correctly parsed and executed by BigQuery, especially when dealing with non-standard dataset names.

[appflowpersiste]

HI ms4446 ,

Thanks for input provided , we have tried above solution in POSTMAN which is not working . 
Please suggest further on this.

CASE 1 : DataSet starting with Number 

Request URL :

https://bigquery.googleapis.com/bigquery/v2/projects/pivotal-trail-356409/queries

Request Body :

{
"query": "SELECT * FROM `998877`.test"
}

Response :

{
"error": {
"code": 404,
"message": "Not found: Dataset pivotal-trail-356409:`998877` was not found in location US",
"errors": [
{
"message": "Not found: Dataset pivotal-trail-356409:`998877` was not found in location US",
"domain": "global",
"reason": "notFound"
}
],
"status": "NOT_FOUND"
}
}

CASE 2 : Dataset Starting with character 

Request URL :

https://bigquery.googleapis.com/bigquery/v2/projects/pivotal-trail-356409/queries

Request Body :

{
"query": "SELECT * FROM BULK123.bigdataset"
}

Response :

Success Response 200 Status code

Note : Both DataSet's are in Same Project ( pivotal-trail-356409 ) .

[ms4446]

When encountering errors querying datasets in BigQuery through Postman, particularly with datasets that start with a number, consider the following steps to diagnose and resolve the issue:

Case 1: Dataset Starting with a Number

The "Not found" error suggests that the dataset might not exist under the specified conditions, or there could be inaccuracies in your project ID, dataset ID, or location specification.

Double-check Dataset Existence:

1. Navigate to the BigQuery console at https://console.cloud.google.com/bigquery.

2. Under your project "pivotal-trail-356409", confirm the existence of a dataset named "998877". Remember, dataset names are case-sensitive.

Confirm Project ID:

• Ensure the project ID in your request URL ("pivotal-trail-356409") accurately matches the project where the dataset resides.

Verify Region (Location):

• The error message mentions "location US". Verify that your dataset "998877" is indeed created in the US region. This detail can be checked in the BigQuery console. If your dataset resides in a different region, you might need to adjust the API request to reflect the correct location.

Case 2: Dataset Starting with a Character

Success in this scenario suggests that your API request format is correct, supporting the likelihood that the issue in Case 1 stems from dataset specifics rather than the request structure.

Additional Considerations

Authentication:

• Ensure your Postman request includes the correct authorization headers for your Google Cloud project. Incorrect or missing authorization can lead to "Not Found" errors.

Permissions:

• Verify that the service account or user making the API call has the necessary permissions to access the datasets. At a minimum, the "BigQuery Data Viewer" role is required.

Debugging Tips

Check BigQuery Web UI:

• The BigQuery Web UI is an excellent starting point for verification. Confirm the exact names and spellings of your datasets directly in the console.

API Explorer:

• Consider using the BigQuery API explorer to test different parameters and see immediate results. This tool can help isolate and identify the issue more effectively.

[appflowpersiste]

Hi  ms4446 ,

As per your suggestion we have created data set under US location and many more location.

But getting error as below.

Request URL:

https://bigquery.googleapis.com/bigquery/v2/projects/pivotal-trail-356409/queries

Request Body:

{
"query": "SELECT * FROM `9090`.UStest"
}

Response :

{
"error": {
"code": 404,
"message": "Not found: Dataset pivotal-trail-356409:`9090 was not found in location us-central1",
"errors": [
{
"message": "Not found: Dataset pivotal-trail-356409:`9090 was not found in location us-central1",
"domain": "global",
"reason": "notFound"
}
],
"status": "NOT_FOUND"
}
}

If possible, can we connect over call??

[kolban]

Try your REST API test from the documentation page here:

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query

If that doesn't help, try "curl" command from Cloud Shell ...

Also .. you are querying a dataset called "9090".  In BigQuery Studio, go to the details of that Dataset and grab a screen shot and post it here.  I for one am very interested in seeing the "Data location" property.  I'm also keen to see the exact "Dataset ID" property.