Notation for creating sample data

[max-sixty]

One annoyance in SQL is that it's quite difficult to create some data in code. This is generally used for sample tables when demonstrating something, or for quick lookup tables.

IME it ends up being something like:

select tim as first_name, smith as last_name, 30 as age
union all
select mary, jones, 31
union all
...

I think we could make this much nicer. The simplest approach would be to just do from_json (or _csv or _yaml), parse the inline string, and then convert to the verbose SQL representation. As above, it's not for loading lots of data; it's just for small samples.

There's possibly a better approach which could include types / structs / arrays etc, though we're somewhat limited by the constraints of SQL. The thought was inspired by @jamii's post's, worth reading (& subscribing!): https://www.scattered-thoughts.net/writing/the-shape-of-data/

[aljazerzen]

I would rather propose we use from for this:

from data:[
 first_name: "tim",
 hobby: "HI",
 age: 30,
]
join ...

From can then be thought as "data source" either as a table, inline data or csv/json/yml

Also there has to be quotes on strings.

[max-sixty]

Great, agree with the from (/ table?) syntax.

With the more literal style, how would be represent multiple rows succinctly? It would be convenient to have a format that people can copy from somewhere else too, as opposed to a PRQL-specific format.

I had started making something like:

from csv:r#"
first_name, last_name, age
tim, smith, 30
mary, jones 31
"#

I had started with CSV since it doesn't need quotes (it wouldn't need the r#""# at first), and is more concise than JSON.

WDYT? Do you still prefer the more "PRQL-native" approach?

[aljazerzen]

Hmmm, why not both? PRQL has nice highlighting, CSV is copy-pastable. There are use cases for both.

Problem with CSV is that there is no standard. comma/semicolon/tab/space, header/no header, spaces after comma are all ambiguous in different software.

Before adding r#""# think twice about how will be include regex literals. Also, in your example string literal starts with new line...

Ah, so much problems with such a simple feature.

[max-sixty]

Before adding r#""# think twice about how will be include regex literals. Also, in your example string literal starts with new line...

I was thinking that r"\w" would be raw text, and the rust approach of r#"foo"# was a good way of extending this to multiple lines! WDYT?

Hmmm, why not both? PRQL has nice highlighting, CSV is copy-pastable. There are use cases for both.

Definitely agree. I do think there can be a temptation to build too many ways of doing something — it's difficult to turn ideas down! But for new users it can be confusing. Generally I would vote to do one thing and then assess if we need another.

Possibly in this case there is a real use for both though.

[manning79]

I do appreciate you keep things clean @max-sixty , I agree with your sentiment of only building one way to do things in the beginning. More ways will come as the language grows but in the beginning if we can keep it light I think it will go far.

That said , I still like the prql approach of from data , but it looks like it would only allow for one row. How many rows do you need in this approach ?

[max-sixty]

How many rows do you need in this approach ?

Generally for sample data I've needed 5-10...

[aljazerzen]

Ah, in this case we would need nested arrays:

from data:[
 [first_name: "tim", hobby: "HI", age: 30],
 [first_name: "tim", hobby: "HI", age: 30],
 [first_name: "tim", hobby: "HI", age: 30],
]
join ...

but now, CSV looks better because it has headers at the top.

[max-sixty]

Something I've noticed that I miss from SQL as I'm writing documentation is the ability to start without a table; e.g. if we want to demonstrate an s-string, we need to have something like:

from employees
derive db_version: s"version()"

...whereas in SQL, that's just SELECT version(); no need for the from.

Hopefully as part of this we can make that easier; e.g.

from data:""
derive db_version: s"version()"

...might be sufficient

[max-sixty]

but now, CSV looks better because it has headers at the top.

CSV is not a great format, but it does have this one advantage! Are there nicer formats that also can have column names specified only once?

[aljazerzen]

SQL has that:

INSERT INTO employees (first_name, age) VALUES
("John", 15),
("Janez", 17),
("Hans", 16)

Pandas has columns paramater of DataFrame constructor. In prql this would look like:

from columns:[first_name, age] data:[
 ["John", 15],
 ["Janez", 17],
 ["Hans", 16],
]
select ...

[aljazerzen]

Also, currently this is a valid query:

derive db_version: s'version()'

SELECT
 version() AS db_version

[max-sixty]

Also, currently this is a valid query:

Ah, I didn't even realize that! That's great!

SQL has that:

INSERT INTO employees (first_name, age) VALUES
("John", 15),
("Janez", 17),
("Hans", 16)

Yes, this is very reasonable. it's a shame that this doesn't extend to inline sample data; and requires table modifications — particularly for analytical DBs where table modifications are expensive & rare.

[max-sixty]

Postgres has something decent for this: https://www.postgresql.org/docs/current/queries-values.html