Closed cjroebuck closed 7 months ago
I think this is because the right syntax for an insert operation here will be:
insert into github_json_ephemeral (message_raw) values ('...')
^ see only message_raw
in the insert clause, which triggers default values for the rest of the fields (https://clickhouse.com/docs/en/sql-reference/statements/create/table#ephemeral).
We can add an option to restrict the insert columns, as it is useful.
The only option I see as a workaround before we add this feature is to use command
with VALUES
instead. It's not the optimal solution, as you might encounter request size limits (since the values are not streamed):
import { createClient } from '@clickhouse/client'
void (async () => {
const table = 'github_json_ephemeral'
const client = createClient({
clickhouse_settings: {
date_time_input_format: 'best_effort',
allow_experimental_object_type: 1,
},
})
await client.command({
query: `
CREATE OR REPLACE TABLE ${table}
(
event_type LowCardinality(String) DEFAULT JSONExtractString(message_raw, 'type'),
repo_name LowCardinality(String) DEFAULT JSONExtractString(message_raw, 'repo', 'name'),
message JSON DEFAULT message_raw,
message_raw String EPHEMERAL
) ENGINE = MergeTree ORDER BY (event_type, repo_name)`,
})
const messagesRaw = [
{
type: 'MyEventType',
repo: {
name: 'foo',
},
},
{
type: 'SomeOtherType',
repo: {
name: 'bar',
},
},
]
// Generates ('val1'),('val2'), ...
const messagesRawValues = messagesRaw
.map((msg) => {
return `('${JSON.stringify(msg)}')`
})
.join(',')
const insertQuery = `INSERT INTO ${table} (message_raw) VALUES ${messagesRawValues}`
await client.command({
query: insertQuery,
})
const result = await client.query({
query: `SELECT * FROM ${table}`,
format: 'JSONCompactEachRow',
})
console.log('Result:', JSON.stringify(await result.json()))
})()
Prints:
Result: [["MyEventType","foo",{"repo":{"name":"foo"},"type":"MyEventType"}],["SomeOtherType","bar",{"repo":{"name":"bar"},"type":"SomeOtherType"}]]
NB: see JSONExtractString(message_raw, 'repo', 'name')
- it should correctly extract the repo name.
@cjroebuck, added in 0.2.8. See the release notes.
Describe the bug
Not sure if this is a clickhouse-js specific bug or just clickhouse in general.
Whenever I try to insert JSON as a raw
String
withEPHEMERAL
column and then have derived columns using various JSONExtract* functions, I can't seem to get it to work. I'm using the example from the JSON docs here.Steps to reproduce
Run the following code to setup tables and insert test rows.
Expected behaviour
Output:
Actual behaviour
The derived columns are blank and the JSON column is empty:
Removing the EPHEMERAL directive from the column, I get the following output:
Notice that
repo_name
field is empty butevent_type
has been successfully extracted from the JSON. So it seems the extract fromrepo.name
is also not working. This is a secondary issue.Configuration
Environment
ClickHouse server