29

I need to make a query for a dataset provided by a public project. I created my own project and added their dataset to my project. There is a table named: domain_public. When I make query to this table I get this error:

 Query Failed
Error: Not found: Dataset my-project-name:domain_public was not found in location US
Job ID: my-project-name:US.bquijob_xxxx

I am from non-US country. What is the issue and how to fix it please?

EDIT 1: I change the processing location to asia-northeast1 (I am based in Singapore) but the same error:

Error: Not found: Dataset censys-my-projectname:domain_public was not found in location asia-northeast1

Here is a view of my project and the public project censys-io:

enter image description here

Please advise.

EDIT 2: The query I used to type is based on censys tutorial is:

#standardsql
SELECT domain, alexa_rank
FROM domain_public.current
WHERE p443.https.tls.cipher_suite = 'some_cipher_suite_goes_here';

When I changed the FROM clause to:

FROM `censys-io.domain_public.current`

And the last line to:

WHERE p443.https.tls.cipher_suite.name = 'some_cipher_suite_goes_here';

It worked. Shall I understand that I should always include the projectname.dataset.table (if I'm using the correct terms) and point the typo the Censys? Or is this special case to this project for some reason?

stevec
  • 41,291
  • 27
  • 223
  • 311
user9371654
  • 2,160
  • 16
  • 45
  • 78
  • what exactly do yo mean by "added their dataset to my project"? – Mikhail Berlyant Jul 18 '18 at 06:50
  • I click on the right arrow next to my project -> switch to project -> display project -> then enter the public project ID. After that I see their datasets and tables in the navigation panel. – user9371654 Jul 18 '18 at 07:34
  • Did you follow these guidelines? Standard SQL only, etc - https://support.censys.io/google-bigquery/bigquery-introduction – J.L Valtueña Jul 18 '18 at 11:27
  • @J.L Valtueña yes I did. – user9371654 Jul 18 '18 at 11:30
  • I tried to add the dataset following this (https://support.censys.io/google-bigquery/adding-censys-datasets-to-bigquery) and is not working. Maybe the dataset was deleted or changed the ID? – J.L Valtueña Jul 18 '18 at 11:41
  • The dataset is added with me correctly. The first test query [here](https://support.censys.io/google-bigquery/adding-censys-datasets-to-bigquery) works fine with me. But the second query `#standardsql SELECT domain, alexa_rank FROM domain_public.20171006 WHERE p443.https.tls.cipher_suite = 'TLS_RSA_WITH_AES_256_CBC_SHA';` (with the exception that I added LIMIT 2 before the semicolon), which is from the same table I want, doe produce the error in this post. – user9371654 Jul 18 '18 at 11:47
  • This query works, but not the one above. `#standardsql SELECT fingerprint_sha256, parsed.names, metadata.updated_at FROM `censys-io.certificates_public.certificates`, UNNEST(parsed.names) AS names WHERE names = 'censys.io` – user9371654 Jul 18 '18 at 11:49
  • The project owners just added my account to give me access and they are still actively running the project. – user9371654 Jul 18 '18 at 11:50

9 Answers9

19

BigQuery can't find your data

How to fix it

Make sure your FROM location contains 3 parts

  1. A project (e.g. bigquery-public-data)
  2. A database (e.g. hacker_news)
  3. A table (e.g. stories)

Like so

`bigquery-public-data.hacker_news.stories`

*note the backticks

Examples

Wrong

SELECT *
FROM `stories`

Wrong

SELECT *
FROM `hacker_news.stories`

Correct

SELECT *
FROM `bigquery-public-data.hacker_news.stories`
stevec
  • 41,291
  • 27
  • 223
  • 311
  • 4
    This does not solve my problem, if you click on QUERY TABLE the UI automatically fills out the query as you posted under "Correct", but it still fails with not found in US – Karl Sep 15 '20 at 00:19
  • @karlpy I haven't used in a little while so I'm a little rusty. Can you double check which region you're using and make sure it matches the region the data is stored in? that's the only other thing I can think of – stevec Sep 15 '20 at 00:20
10

In Web UI - click Show Options button and than select your location for "Processing Location"!

Specify the location in which the query will execute. Queries that run in a specific location may only reference data in that location. For data in US/EU, you may choose Unspecified to run the query in the location where the data resides. For data in other locations, you must specify the query location explicitly.

Update

As it stated above - Queries that run in a specific location may only reference data in that location

Assuming that censys-io.domain_public dataset has its data in US - you need to specify US for Processing Location

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • I went to the `show options`. I found US, EU, asia-northeast1 and unspecified. I am based in asia at the moment so I picked the asia-northeast. But when I run the query I get: `Query Failed Error: Not found: Dataset censys-io:domain_public was not found in location asia-northeast1 Job ID:my-projectname:asia-northeast1.bquijob_4a55c23d_xxx – user9371654 Jul 18 '18 at 07:27
  • If the dataset is not actually stored in asia-northeast-1, then you can't set that as the processing location and expect it to work. Click on the dataset in the UI and I suspect that you will see a different location. – Elliott Brossard Jul 18 '18 at 12:40
  • I also get this error when the table doesn't exist (if, for example, i search for table `asdfghjkl` in the dataset, so don't be too thrown off by the error about the processing location. – A N Jun 05 '19 at 17:47
  • I control + f searched 'show options' and cannot find any, also checked the page source. Has this changed? (it's a very busy page) – stevec Nov 24 '19 at 12:39
  • 2
    @user5783745 - original answer is for so called Classic UI. You most likely looking into new UI - BigQuery Console - in this New UI you should click on More ... button and then Query Settings. In opened panel you will be able to find location related settings – Mikhail Berlyant Nov 24 '19 at 17:17
3

The problem turned out to be due to wrong table name in the FROM clause. The right FROM clause should be:

FROM `censys-io.domain_public.current`

While I was typing:

FROM domain_public.current

So the project name is required in the FROM and `` are required because of - in the project name.

WeNeigh
  • 3,489
  • 3
  • 27
  • 40
user9371654
  • 2,160
  • 16
  • 45
  • 78
1

Make sure your FROM location contains 3 parts as @stevec mentioned

  • A project (e.g. bigquery-public-data)
  • A database (e.g. hacker_news)
  • A table (e.g. stories)

But in my case, I was using the LegacySql within the Google script editor, so in that case you need to state that to false, for example:

var projectId = 'xxxxxxx';
    
var request = {
  query: 'select * from project.database.table',
  useLegacySql: false
};
var queryResults = BigQuery.Jobs.query(request, projectId);
Ruben Portz
  • 174
  • 1
  • 10
1

check exact case [upper or lower] and spelling of table or view name. copy it from table definition and your problem will be solved.

i was using FPL009_Year_Categorization instead of FPL009_Year_categorization

using c as C and getting the error "not found in location asia-south1"

I copied with exact case and problem is resolved.

Brij
  • 11
  • 1
1

create the dataset as a "multi-region" and as US (Multiple regions in the United States) ,this should solve the issue.

Mahendra Bhandarkar
  • 11
  • 1
0

On your Big Query console, go to the Data Explorer on the left pane, click the small three dots, then select query option from the list. This step confirms you choose the correct project and dataset. Then you can edit the query on the query pane on the right.

corngk
  • 31
  • 4
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 17 '21 at 05:02
0

may be dataset name changed in create dataset option. it should be US or default location

enter image description here

Shibin K Abraham
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 18 '22 at 03:39
0

In my case there was a very strange thing - it was the same error but the solution was to add a space after a table name:

`vld3gda.warehouse_orders.orders` -- error
`vld3gda.warehouse_orders.orders ` -- no error
45825243
  • 1
  • 2