How to specify schema/dataset for persisting tables?

[dimitri-b]

preql can persist tables using table command, but it is not clear which schema/dataset this table would be added to, if connected to a database that supports/uses schemas.

[erezsh]

This behavior isn't implemented yet. Creating a table will create it in the default schema.

I can adopt the postgres syntax:

table my_schema.my_table = expression

Would that be sufficient for your purposes?

Are you using schemas heavily in your flow? If so, I will try to add full support soon.

[dimitri-b]

It worked for me in REPL, but I had to:
a) specify datasets for created tables, e.g. table a.A {x: int}, and
b) add the id column, otherwise I got id cannot be null error if there is any data in the source table

It seems that if dataset is not specified, BQ lands the table in a random dataset of its choice, if there are several datasets. In my case it was the first listed dataset in the connected project.

I'm not sure why in your case the SELECT statement works, but INSERT doesn't.

 >> table test_data.A {x: int}
(Query took 2.37 seconds)
 >> table test_data.B {x: int}
(Query took 2.25 seconds)
 >> SQL(nulltype, """INSERT INTO `test_data`.`A`(`x`) SELECT `x` FROM `test_data`.`B`  """)
null
(Query took 2.90 seconds)
 >>

[erezsh]

@dimitri-b Just wanted to let you know, there is now full support for BigQuery in Preql (in the master branch. I'll make a release soon).

That is, all the tests I wrote are now passing. You might still find bugs, but it's probably going to happen a lot less.

I also opted to using a temporary _preql dataset for tables that aren't associated with a dataset, instead of choosing the first on the list. That means I create and delete the _preql dataset. Let me know if you think that's a bad idea.

[dimitri-b]

Defaulting to _preql dataset is a good idea, the only suggestions I have are:

• some users may not have the right permissions to create datasets, so a descriptive error message would be needed to save frustration
• on a remote chance that there is already a dataset with that name/id - what would you do? Overwrite or just use it? Delete it on session end? Another option would be to name the dataset using UUID or similar, but I don't like the sound of it, and it is probably not worth it.

[erezsh]

I suppose I can rename it to _preql_432492834903280 or something like that. It felt to me that _preql is probably already unique enough. But since I would be deleting it without ceremony, perhaps it's better to take a very cautious approach.

[dimitri-b]

I think _preql_432492834903280 pattern is a good approach - it would be clear to anyone seeing it that this is a temporary dataset managed by Preql.

[dimitri-b]

In case of BigQuery - our typical use case is to query a table from one dataset and save the results in another. We use and specify schema/dataset all the time. We also switch between dev/prod datasets/tables, via templating the SQL files.