[v2.0.0-alpha.10] BigQuery doesn't connect to regions that are not US #6937

gusfune · 2021-05-17T07:24:30Z

BigQuery only connects to datasets hosted in the US region.
I could not connect as my datasets are in europe-west3. There are other regions as well that needs.

The text was updated successfully, but these errors were encountered:

0x777 · 2021-05-18T08:35:13Z

@gusfune Thanks for reporting this. We are looking into this and we will get back to you as soon as possible.

chrisdone · 2021-05-21T11:01:40Z

Hi Gus,

(I'll assume that this cascade of errors by Google Cloud is unrelated.)

This is not an expected behavior -- we don't put any region information in the backend's communication with the BigQuery APIs.

Can you share the error message that you're seeing?

One possible explanation is that the service account is restricted to a region, but I'd have to see more information.

gusfune · 2021-05-21T13:00:53Z

Hi @chrisdone I will be glad to help.
Here is what happens: I can add the database to Hasura, but after it says it was added, I start getting the following error:

DB schema loading failed
RESTRequestNonOK (Status {statusCode = 404, statusMessage = "Not Found"}) "{\n \"error\": {\n \"code\": 404,\n \"message\": \"Not found: Dataset co-courate:undefined was not found in location US\",\n \"errors\": [\n {\n \"message\": \"Not found: Dataset co-courate:undefined was not found in location US\",\n \"domain\": \"global\",\n \"reason\": \"notFound\"\n }\n ],\n \"status\": \"NOT_FOUND\"\n }\n}\n"

Here is two screenshots:

Here is also the error captured from the logs:

graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T12:59:59.204+0000","level":"error","detail":{"operation":{"user_vars":{"x-hasura-role":"admin"},"error":{"path":"$[0]","error":"RESTRequestNonOK (Status {statusCode = 404, statusMessage = \"Not Found\"}) \"{\\n  \\\"error\\\": {\\n    \\\"code\\\": 404,\\n    \\\"message\\\": \\\"Not found: Dataset co-courate:undefined was not found in location US\\\",\\n    \\\"errors\\\": [\\n      {\\n        \\\"message\\\": \\\"Not found: Dataset co-courate:undefined was not found in location US\\\",\\n        \\\"domain\\\": \\\"global\\\",\\n        \\\"reason\\\": \\\"notFound\\\"\\n      }\\n    ],\\n    \\\"status\\\": \\\"NOT_FOUND\\\"\\n  }\\n}\\n\"","code":"bigquery-error"},"request_id":"4664c576-ab20-4175-9b6e-6c1a1f0a4775","response_size":574,"query":{"args":[{"args":{"source":"bigquery","sql":"select\n    t.table_schema as table_schema,\n    t.table_name as table_name,\n    t.table_type as table_type, \n    opts.option_value as comment,\n    CONCAT(\"[\", c.json_data ,\"]\") as columns  \n    FROM undefined.INFORMATION_SCHEMA.TABLES as t\n    LEFT JOIN \n    (\n    with x as (\n        select table_name, table_schema, column_name, ordinal_position, is_nullable, data_type from undefined.INFORMATION_SCHEMA.COLUMNS\n    ) select x.table_name as table_name, x.table_schema as table_schema, STRING_AGG(TO_JSON_STRING(x)) as json_data from x group by x.table_name,x.table_schema\n    ) as c\n    ON c.table_name = t.table_name and t.table_schema = c.table_schema\n    LEFT JOIN undefined.INFORMATION_SCHEMA.TABLE_OPTIONS as opts\n    ON opts.table_name = t.table_name and opts.table_schema = t.table_schema and opts.option_name = \"description\";"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"}],"type":"bulk"}},"request_id":"4664c576-ab20-4175-9b6e-6c1a1f0a4775","http_info":{"status":400,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":null}}}

Extra info:

This BigQuery dataset I want to connect was created via Segment.
BigQuery can have both multi-region or single-region datasets. From the looks of my Segment configuration, it seems they create automatically as a single-region dataset:

Let me know if you need more info.

chrisdone · 2021-05-21T13:25:29Z

The name is odd: co-courate:undefined - is it really called "co-courate:undefined"?

This feels like a JavaScript bug that has leaked into the system.

Can you confirm whether your dataset is really named co-curate:undefined? If not, what's the full name of your dataset?

gusfune · 2021-05-21T14:31:01Z

Huh. Actually the dataset is co-courate:website.
I did a new test, when pushing the database, the metadata request goes correctly (checking from Network tab):

{
	"args": {
		"configuration": {
			"datasets": [
				"website"
			],
			"project_id": "co-courate",
			"service_account": "REDACTED"
		},
		"name": "bigtest"
	},
	"type": "bigquery_add_source"
}

It responds: message "success"
Then the next metadata request:

{
	"resource_version": 7,
	"metadata": {
		"version": 3,
		"sources": [
			{
				"name": "bigtest",
				"kind": "bigquery",
				"tables": [],
				"configuration": {
					"service_account": {
						"project_id": "co-courate",
						"client_email": "REDACTED",
						"private_key": "REDACTED"
					},
					"project_id": "co-courate",
					"datasets": [
						"website"
					]
				}
			}
		]
	}
}

Then the query request goes:

error: RESTRequestNonOK (Status {statusCode = 404, statusMessage = "Not Found"}) "{\n  \"error\": {\n    \"code\": 404,\n    \"message\": \"Not found: Dataset co-courate:undefined was not found in location US\",\n    \"errors\": [\n      {\n        \"message\": \"Not found: Dataset co-courate:undefined was not found in location US\",\n        \"domain\": \"global\",\n        \"reason\": \"notFound\"\n      }\n    ],\n    \"status\": \"NOT_FOUND\"\n  }\n}\n"

0x777 · 2021-05-21T15:02:51Z

@beerose Does the console make any bigquery_run_sql queries?

0x777 · 2021-05-21T15:17:18Z

@gusfune Are you certain that the query that you have shared is what fails? What code does the server return?

gusfune · 2021-05-21T15:33:02Z

@0x777 I am certain that the query that is failing is this one (Sending below in more details). To replicate the issue this is what happens:

I open the console in databases
I add a new database, filling the information according to the instructions from firebase2graphql (v0.0.2): support array types in firebase json (close #1829) #1900.
Monitoring the network tab, the metadata insertion goes correct with the datasets (not undefined), but the first query that returns is the one throwing the error, and containing undefined as the not found dataset.

I did some extra snooping around and on hdb_metadata the datasets are correctly set:
{"sources":[{"kind":"postgres","name":"default","tables":[],"configuration":{"connection_info":{"use_prepared_statements":true,"database_url":{"from_env":"HASURA_GRAPHQL_DATABASE_URL"},"isolation_level":"read-committed","pool_settings":{"connection_lifetime":600,"retries":1,"idle_timeout":180,"max_connections":50}}}},,{"kind":"bigquery","name":"bigtest","tables":[],"configuration":{"service_account":{"project_id":"co-courate","client_email":"[email protected]","private_key":"REDACTED"},"project_id":"co-courate","datasets":["website"]}}],"version":3}

Here is the query that returns 400:
REQUEST:

POST /v2/query HTTP/1.1
Host: localhost:8080
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:88.0) Gecko/20100101 Firefox/88.0
Accept: */*
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Referer: http://localhost:8080/console/data/bigquery/schema/undefined
content-type: application/json
x-hasura-admin-secret: REDACTED
Origin: http://localhost:8080
Content-Length: 1439
DNT: 1
Connection: keep-alive
Cookie: gusfune_sAnonymousSessionToken=REDACTED

RESPONSE:

{"path":"$[0]","error":"RESTRequestNonOK (Status {statusCode = 404, statusMessage = \"Not Found\"}) \"{\\n  \\\"error\\\": {\\n    \\\"code\\\": 404,\\n    \\\"message\\\": \\\"Not found: Dataset co-courate:undefined was not found in location US\\\",\\n    \\\"errors\\\": [\\n      {\\n        \\\"message\\\": \\\"Not found: Dataset co-courate:undefined was not found in location US\\\",\\n        \\\"domain\\\": \\\"global\\\",\\n        \\\"reason\\\": \\\"notFound\\\"\\n      }\\n    ],\\n    \\\"status\\\": \\\"NOT_FOUND\\\"\\n  }\\n}\\n\"","code":"bigquery-error"}

Here is also a full log output from starting the instance to the moment I get the error query:

graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:23:20.459+0000","level":"info","detail":{"kind":"server_configuration","info":{"live_query_options":{"batch_size":100,"refetch_delay":1},"transaction_isolation":"ISOLATION LEVEL READ COMMITTED","plan_cache_options":{"plan_cache_size":4000},"enable_maintenance_mode":false,"enabled_log_types":["http-log","websocket-log","startup","webhook-log","query-log"],"server_host":"HostAny","enable_allowlist":false,"remote_schema_permissions":false,"log_level":"info","auth_hook_mode":null,"use_prepared_statements":true,"unauth_role":null,"stringify_numeric_types":false,"v1-boolean-null-collapse":false,"enabled_apis":["metadata","graphql","config","pgdump"],"enable_telemetry":true,"enable_console":true,"auth_hook":null,"infer_function_permissions":true,"experimental_features":[],"events_fetch_batch_size":100,"jwt_secret":null,"cors_config":{"allowed_origins":"*","disabled":false,"ws_read_cookie":null},"websocket_compression_options":"NoCompression","console_assets_dir":null,"admin_secret_set":true,"port":8080,"websocket_keep_alive":"KeepAliveDelay {unKeepAliveDelay = Seconds {seconds = 5s}}"}}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:23:20.459+0000","level":"info","detail":{"kind":"postgres_connection","info":{"retries":1,"database_url":"postgres://postgres:...@postgres:5432/postgres"}}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"catalog_migrate","info":"Already at the latest catalog version (46); nothing to do."}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"schema-sync","info":"Schema sync enabled. Polling at Milliseconds {milliseconds = 1s}"}}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread SchemeUpdate.listener (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread SchemeUpdate.processor (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread asyncActionsProcessor (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread runCronEventsGenerator (re)started"}
graphql-engine_1  | {"type":"schema-sync-thread","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"thread_type":"processor","info":{"currentVersion":0,"latestResourceVersion":7}}}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread processScheduledTriggers (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread checkForUpdates (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread runTelemetry (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread ourIdleGC (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread processEventQueue (re)started"}
graphql-engine_1  | {"type":"unstructured","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":"Thread asyncActionSubscriptionsProcessor (re)started"}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"schema-sync","info":{"thread_id":"ThreadId 93","instance_id":"ae40afe3-2c7e-4d97-82f1-0e4dcef2d2fc","message":"listener thread started"}}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"schema-sync","info":{"thread_id":"ThreadId 95","instance_id":"ae40afe3-2c7e-4d97-82f1-0e4dcef2d2fc","message":"processor thread started"}}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"event_triggers","info":"starting workers"}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"scheduled_triggers","info":"preparing data"}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"telemetry","info":"Help us improve Hasura! The graphql-engine server collects anonymized usage stats which allows us to keep improving Hasura at warp speed. To read more or opt-out, visit https://hasura.io/docs/latest/graphql/core/guides/telemetry.html"}}
graphql-engine_1  | {"type":"startup","timestamp":"2021-05-21T15:24:02.034+0000","level":"info","detail":{"kind":"server","info":{"time_taken":41.61745254,"message":"starting API server"}}}
graphql-engine_1  | {"type":"schema-sync-thread","timestamp":"2021-05-21T15:24:41.272+0000","level":"info","detail":{"thread_type":"processor","info":{"message":"Schema Version changed with notifications"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":2.227965e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"3bbfe1b4-2e2b-4917-b8b4-0286aa7da3c4","response_size":60,"request_read_time":4.484e-6},"request_id":"3bbfe1b4-2e2b-4917-b8b4-0286aa7da3c4","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"query-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"kind":"introspection","request_id":"648eabae-e6e1-4398-b84f-d82aac2cfd0e","generated_sql":null,"query":{"query":"\n    query IntrospectionQuery {\n      __schema {\n        queryType { name }\n        mutationType { name }\n        subscriptionType { name }\n        types {\n          ...FullType\n        }\n        directives {\n          name\n          description\n          locations\n          args {\n            ...InputValue\n          }\n        }\n      }\n    }\n\n    fragment FullType on __Type {\n      kind\n      name\n      description\n      fields(includeDeprecated: true) {\n        name\n        description\n        args {\n          ...InputValue\n        }\n        type {\n          ...TypeRef\n        }\n        isDeprecated\n        deprecationReason\n      }\n      inputFields {\n        ...InputValue\n      }\n      interfaces {\n        ...TypeRef\n      }\n      enumValues(includeDeprecated: true) {\n        name\n        description\n        isDeprecated\n        deprecationReason\n      }\n      possibleTypes {\n        ...TypeRef\n      }\n    }\n\n    fragment InputValue on __InputValue {\n      name\n      description\n      type { ...TypeRef }\n      defaultValue\n    }\n\n    fragment TypeRef on __Type {\n      kind\n      name\n      ofType {\n        kind\n        name\n        ofType {\n          kind\n          name\n          ofType {\n            kind\n            name\n            ofType {\n              kind\n              name\n              ofType {\n                kind\n                name\n                ofType {\n                  kind\n                  name\n                  ofType {\n                    kind\n                    name\n                  }\n                }\n              }\n            }\n          }\n        }\n      }\n    }\n  "}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":7.584687e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"648eabae-e6e1-4398-b84f-d82aac2cfd0e","response_size":898,"request_read_time":8.144e-6},"request_id":"648eabae-e6e1-4398-b84f-d82aac2cfd0e","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/graphql","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":1.373431e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"4783430f-3f5e-4fc4-9d10-1228f03a0b37","response_size":1776,"request_read_time":6.043e-6},"request_id":"4783430f-3f5e-4fc4-9d10-1228f03a0b37","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":2.347876e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"62ebfa3b-a38a-4df9-b7d8-c674c421315d","response_size":60,"request_read_time":8.258e-6},"request_id":"62ebfa3b-a38a-4df9-b7d8-c674c421315d","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":3.989262e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"3b785948-19af-4552-bb96-b525fc45e736","response_size":219,"request_read_time":5.458e-6},"request_id":"3b785948-19af-4552-bb96-b525fc45e736","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":7.5017e-5,"user_vars":{"x-hasura-role":"admin"},"request_id":"c8607a48-77eb-421a-bd92-8bce1063f162","response_size":220,"request_read_time":5.125e-6},"request_id":"c8607a48-77eb-421a-bd92-8bce1063f162","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1alpha1/config","ip":"172.18.0.1","method":"GET","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:46.422+0000","level":"info","detail":{"operation":{"query_execution_time":8.1534e-5,"user_vars":{"x-hasura-role":"admin"},"request_id":"897eaf9b-d575-4a59-a545-812f6c37050e","response_size":220,"request_read_time":6.533e-6},"request_id":"897eaf9b-d575-4a59-a545-812f6c37050e","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1alpha1/config","ip":"172.18.0.1","method":"GET","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":4.679914e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"825e0190-8cc7-4252-a3ae-5fcb20878ccf","response_size":219,"request_read_time":7.574e-6},"request_id":"825e0190-8cc7-4252-a3ae-5fcb20878ccf","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":1.208679e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"b3c8b952-1187-47fd-aff7-eda305ed5d92","response_size":39,"request_read_time":6.008e-6},"request_id":"b3c8b952-1187-47fd-aff7-eda305ed5d92","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":2.0449092e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"22c1ce68-c7c1-4649-88cc-3b9b92672b83","response_size":22,"request_read_time":5.721e-6},"request_id":"22c1ce68-c7c1-4649-88cc-3b9b92672b83","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":1.167915e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"0078c244-615b-422d-b34d-426c2622c3fd","response_size":22,"request_read_time":6.251e-6},"request_id":"0078c244-615b-422d-b34d-426c2622c3fd","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":1.226765e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"b276346d-b9da-4f0a-be88-625197a0b342","response_size":1776,"request_read_time":3.664e-6},"request_id":"b276346d-b9da-4f0a-be88-625197a0b342","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:48.846+0000","level":"info","detail":{"operation":{"query_execution_time":8.2243006e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"8d9fcbdf-36ca-4fae-afda-461d5244af30","response_size":1696,"request_read_time":5.011e-6},"request_id":"8d9fcbdf-36ca-4fae-afda-461d5244af30","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:48.846+0000","level":"info","detail":{"operation":{"query_execution_time":0.136840228,"user_vars":{"x-hasura-role":"admin"},"request_id":"06088df2-5bd0-41d4-8d6a-9a8a4e8c977d","response_size":84,"request_read_time":7.437e-6},"request_id":"06088df2-5bd0-41d4-8d6a-9a8a4e8c977d","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":2.055268e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"049c017b-9e50-4de0-8bae-f3dcbc97e7d7","response_size":160,"request_read_time":5.185e-6},"request_id":"049c017b-9e50-4de0-8bae-f3dcbc97e7d7","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":3.6780661e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"704563a1-d062-45ce-bb14-a1aa8bf09ddf","response_size":79,"request_read_time":8.554e-6},"request_id":"704563a1-d062-45ce-bb14-a1aa8bf09ddf","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:47.828+0000","level":"info","detail":{"operation":{"query_execution_time":1.3019149e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"3a8a651d-a115-42f4-8f09-960900143044","response_size":219,"request_read_time":1.7771e-5},"request_id":"3a8a651d-a115-42f4-8f09-960900143044","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:25:48.846+0000","level":"info","detail":{"operation":{"query_execution_time":1.3014319e-2,"user_vars":{"x-hasura-role":"admin"},"request_id":"7f828c61-d2a0-420e-afba-d30b9b52bb04","response_size":107,"request_read_time":6.148e-6},"request_id":"7f828c61-d2a0-420e-afba-d30b9b52bb04","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:28:01.638+0000","level":"info","detail":{"operation":{"query_execution_time":1.225830775,"user_vars":{"x-hasura-role":"admin"},"request_id":"76121477-55f4-450c-ad66-2d648bfe8c1f","response_size":55,"request_read_time":2.5065e-5},"request_id":"76121477-55f4-450c-ad66-2d648bfe8c1f","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:28:01.638+0000","level":"info","detail":{"operation":{"query_execution_time":1.362632e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"6329296b-2f50-41cb-8828-744a184f0524","response_size":1776,"request_read_time":6.492e-6},"request_id":"6329296b-2f50-41cb-8828-744a184f0524","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:28:01.638+0000","level":"info","detail":{"operation":{"query_execution_time":1.490338e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"a845be23-796c-4c9a-bc6b-5caddd4a895a","response_size":22,"request_read_time":4.9e-6},"request_id":"a845be23-796c-4c9a-bc6b-5caddd4a895a","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:28:02.749+0000","level":"error","detail":{"operation":{"user_vars":{"x-hasura-role":"admin"},"error":{"path":"$[0]","error":"RESTRequestNonOK (Status {statusCode = 404, statusMessage = \"Not Found\"}) \"{\\n  \\\"error\\\": {\\n    \\\"code\\\": 404,\\n    \\\"message\\\": \\\"Not found: Dataset co-courate:undefined was not found in location US\\\",\\n    \\\"errors\\\": [\\n      {\\n        \\\"message\\\": \\\"Not found: Dataset co-courate:undefined was not found in location US\\\",\\n        \\\"domain\\\": \\\"global\\\",\\n        \\\"reason\\\": \\\"notFound\\\"\\n      }\\n    ],\\n    \\\"status\\\": \\\"NOT_FOUND\\\"\\n  }\\n}\\n\"","code":"bigquery-error"},"request_id":"afdd7eae-c4a1-48b3-b8ec-46d6407dc960","response_size":574,"query":{"args":[{"args":{"source":"bigquery","sql":"select\n    t.table_schema as table_schema,\n    t.table_name as table_name,\n    t.table_type as table_type, \n    opts.option_value as comment,\n    CONCAT(\"[\", c.json_data ,\"]\") as columns  \n    FROM undefined.INFORMATION_SCHEMA.TABLES as t\n    LEFT JOIN \n    (\n    with x as (\n        select table_name, table_schema, column_name, ordinal_position, is_nullable, data_type from undefined.INFORMATION_SCHEMA.COLUMNS\n    ) select x.table_name as table_name, x.table_schema as table_schema, STRING_AGG(TO_JSON_STRING(x)) as json_data from x group by x.table_name,x.table_schema\n    ) as c\n    ON c.table_name = t.table_name and t.table_schema = c.table_schema\n    LEFT JOIN undefined.INFORMATION_SCHEMA.TABLE_OPTIONS as opts\n    ON opts.table_name = t.table_name and opts.table_schema = t.table_schema and opts.option_name = \"description\";"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"}],"type":"bulk"}},"request_id":"afdd7eae-c4a1-48b3-b8ec-46d6407dc960","http_info":{"status":400,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.18.0.1","method":"POST","content_encoding":null}}}
graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-21T15:28:08.548+0000","level":"info","detail":{"operation":{"query_execution_time":1.574243e-3,"user_vars":{"x-hasura-role":"admin"},"request_id":"13055623-feef-43f9-b736-25de980100fc","response_size":1776,"request_read_time":5.405e-6},"request_id":"13055623-feef-43f9-b736-25de980100fc","http_info":{"status":200,"http_version":"HTTP/1.1","url":"/v1/metadata","ip":"172.18.0.1","method":"POST","content_encoding":"gzip"}}}

…from a non-US region Fixes [issue](#6937) ### Description The problem lies with the `schemaListSql` for Big Query. Without a region qualifier prefixed, the SQL result contains datasets only US regions, as described in the [docs](https://cloud.google.com/bigquery/docs/information-schema-datasets). ``` If you do not specify a regional qualifier, metadata is retrieved from the us multi-region. ``` The alternative way is to get a list of all available publicly available regions (which is `us` and `europe` atm), for example for europe - ``` SELECT * FROM region-europe.INFORMATION_SCHEMA.SCHEMATA; ``` and then perform `UNION ALL` of the results. This is approached is ditched in favour of the reason that Big Query is read-only on Hasura and new datasets cannot be added from the console (other than by modifying the metadata), we can return the datasets available in the Big Query source's metadata as the possible values of datasets needed by the console to render the data nav tree, thereby avoiding an extra API call in the process. GitOrigin-RevId: 8117d56

beerose · 2021-05-25T18:54:59Z

Fixed in 3acfd35

0x777 · 2021-05-26T15:10:39Z

@gusfune Can you try this build which has the fix: hasurabuild/graphql-engine:mono-branch-main-b67e577b1? Unfortunately, this landed after we released alpha.11.

gusfune · 2021-05-26T15:34:18Z

It did the connection @0x777 , but now the error is:

DB schema loading failed
source with name "bigquery" is inconsistent

In the console I get:

graphql-engine_1  | {"type":"http-log","timestamp":"2021-05-26T15:31:35.610+0000","level":"error","detail":{"operation":{"user_vars":{"x-hasura-role":"admin"},"error":{"path":"$[0]","error":"source with name \"bigquery\" is inconsistent","code":"unexpected"},"request_id":"1e311eea-dfa1-4487-97da-7eba69144363","response_size":91,"query":{"args":[{"args":{"source":"bigquery","sql":"select\n    t.table_schema as table_schema,\n    t.table_name as table_name,\n    t.table_type as table_type, \n    opts.option_value as comment,\n    CONCAT(\"[\", c.json_data ,\"]\") as columns  \n    FROM website.INFORMATION_SCHEMA.TABLES as t\n    LEFT JOIN \n    (\n    with x as (\n        select table_name, table_schema, column_name, ordinal_position, is_nullable, data_type from website.INFORMATION_SCHEMA.COLUMNS\n    ) select x.table_name as table_name, x.table_schema as table_schema, STRING_AGG(TO_JSON_STRING(x)) as json_data from x group by x.table_name,x.table_schema\n    ) as c\n    ON c.table_name = t.table_name and t.table_schema = c.table_schema\n    LEFT JOIN website.INFORMATION_SCHEMA.TABLE_OPTIONS as opts\n    ON opts.table_name = t.table_name and opts.table_schema = t.table_schema and opts.option_name = \"description\";"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"},{"args":{"source":"bigquery","sql":"select [];"},"type":"bigquery_run_sql"}],"type":"bulk"}},"request_id":"1e311eea-dfa1-4487-97da-7eba69144363","http_info":{"status":400,"http_version":"HTTP/1.1","url":"/v2/query","ip":"172.20.0.1","method":"POST","content_encoding":null}}}

Maybe it's a different issue. Then I am not sure. Might be related to the fact it's a pre-existing database.

0x777 · 2021-06-02T09:31:36Z

@gusfune I think it'll be easier for us to debug this if we can reproduce this on our end. I tried creating a new dataset, but europe-west3 doesn't show up:

gusfune · 2021-06-02T10:49:43Z

I will be glad to help out @0x777, do you want to hop in a google meets anytime soon? https://calendly.com/gusfune

eviefp · 2022-06-09T13:48:36Z

This was fixed, confirmed with @0x777 on slack

0x777 added the a/data/bigquery label May 18, 2021

beerose closed this as completed May 25, 2021

0x777 reopened this Jun 2, 2021

eviefp closed this as completed Jun 9, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[v2.0.0-alpha.10] BigQuery doesn't connect to regions that are not US #6937

[v2.0.0-alpha.10] BigQuery doesn't connect to regions that are not US #6937

gusfune commented May 17, 2021

0x777 commented May 18, 2021

chrisdone commented May 21, 2021

gusfune commented May 21, 2021

chrisdone commented May 21, 2021

gusfune commented May 21, 2021

0x777 commented May 21, 2021

0x777 commented May 21, 2021 •

edited

Loading

gusfune commented May 21, 2021

beerose commented May 25, 2021

0x777 commented May 26, 2021

gusfune commented May 26, 2021

0x777 commented Jun 2, 2021

gusfune commented Jun 2, 2021

eviefp commented Jun 9, 2022

[v2.0.0-alpha.10] BigQuery doesn't connect to regions that are not US #6937

[v2.0.0-alpha.10] BigQuery doesn't connect to regions that are not US #6937

Comments

gusfune commented May 17, 2021

0x777 commented May 18, 2021

chrisdone commented May 21, 2021

gusfune commented May 21, 2021

chrisdone commented May 21, 2021

gusfune commented May 21, 2021

0x777 commented May 21, 2021

0x777 commented May 21, 2021 • edited Loading

gusfune commented May 21, 2021

beerose commented May 25, 2021

0x777 commented May 26, 2021

gusfune commented May 26, 2021

0x777 commented Jun 2, 2021

gusfune commented Jun 2, 2021

eviefp commented Jun 9, 2022

0x777 commented May 21, 2021 •

edited

Loading