You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I tried to run a query CREATE TABLE AS SELECT with the BigQuery emulator and it failed with the error:
Failed to create compacted log: 500 failed to analyze: INVALID_ARGUMENT: CREATE TABLE AS SELECT with column definition list is unsupported [at 2:1]; message: failed to analyze: INVALID_ARGUMENT: CREATE TABLE AS SELECT with column definition list is unsupported [at 2:1], reason: jobInternalError
What did you expect to happen?
The BigQuery emulator should be able to support queries CREATE TABLE AS SELECT that include column definition lists.
How can we reproduce it (as minimally and precisely as possible)?
You can reproduce this error by running a query like this one:
CREATETABLEIF NOT EXISTS {{project}}.{{dataset}}.table2
(
field_x STRING NOT NULL,
field_y STRING,
field_z STRING
)
AS (
SELECT
field_a,
field_b,
field_c
FROM`{{project}}.{{dataset}}.table1`
)
Anything else we need to know?
I think this is a limitation with SQLite and not an error with the codebase of the BigQuery emulator, after reading the docs https://www.sqlite.org/lang_createtable.html I think it is not possible to pass the column definitions (see points 2 and 3.1).
I still think the BigQuery emulator should take care of this because the emulator's purpose is to convert BigQuery compatible SQL to a SQLite compatible version that produces the same outcome.
One solution can be split the CREATE TABLE AS SELECT into two queries, one that creates the table, and the other one that runs a INSERT INTO [table] SELECT (I tested the last one and it works).
Having this feature CREATE TABLE AS SELECT is important because it allows to define the name of the column and if it allows null values.
Thank you for your work and for setting this BigQuery emulator, it is an excellent project!
The text was updated successfully, but these errors were encountered:
What happened?
I tried to run a query
CREATE TABLE AS SELECT
with the BigQuery emulator and it failed with the error:What did you expect to happen?
The BigQuery emulator should be able to support queries
CREATE TABLE AS SELECT
that include column definition lists.How can we reproduce it (as minimally and precisely as possible)?
You can reproduce this error by running a query like this one:
Anything else we need to know?
I think this is a limitation with SQLite and not an error with the codebase of the BigQuery emulator, after reading the docs https://www.sqlite.org/lang_createtable.html I think it is not possible to pass the column definitions (see points 2 and 3.1).
I still think the BigQuery emulator should take care of this because the emulator's purpose is to convert BigQuery compatible SQL to a SQLite compatible version that produces the same outcome.
One solution can be split the
CREATE TABLE AS SELECT
into two queries, one that creates the table, and the other one that runs aINSERT INTO [table] SELECT
(I tested the last one and it works).Having this feature
CREATE TABLE AS SELECT
is important because it allows to define the name of the column and if it allows null values.Thank you for your work and for setting this BigQuery emulator, it is an excellent project!
The text was updated successfully, but these errors were encountered: