2b:null 2c:["$","title",null,{"children":"DuckDB Parser experiments | José M. Zavala González | Observable"}] 31:T12e9,query_script_examples = [ "select 1", "select * from range(0, 10)", "select * from duckdb_tables", "select frog from frogs", "select frog from frogs where height > 5 and leader = true", "select 1 * 1", "select frog.age from frogs", "select []::boolean[]", "select frog.* EXCLUDE age from frogs", ` SELECT * EXCLUDE (timestamp_tz) REPLACE (varchar.replace(chr(0), chr(10)) AS whatever) FROM test_all_types() `, "select list_apply([1, 2, 3], x => x * 2)", "select 0::DECIMAL(15, 6)", "select 0::USER_TYPE", "select 0::STRUCT(a INT)", "select name from frogs GROUP BY age", "SELECT * FROM frogs USING SAMPLE 1% (BERNOULLI);", ` select (select 1) as one `, ` SELECT city, COUNT(*) FROM addresses GROUP BY city HAVING COUNT(*) >= 50; `, "SELECT 1 < 1, 1 <= 2", "SELECT '101010'::BIT", "SELECT 0::HUGEINT", "SELECT 0::UNION(num INT, str VARCHAR)", // r"SELECT '\xAA'::BLOB", "SELECT INTERVAL 1 YEAR", "SELECT NULL IS NULL", "SELECT DATE '1992-09-20'", "SELECT TIMESTAMP '1992-09-20 11:30:00'", "SELECT TIMESTAMPTZ '1992-09-20 11:30:00'", "SELECT i, CASE WHEN i>2 THEN 1 ELSE 0 END AS test FROM integers", "SELECT 'hello' COLLATE NOCASE", "SELECT 'Math' IN ('CS', 'Math'), X NOT IN ('CS', 'Math')", "SELECT a BETWEEN x AND y", "SELECT a NOT BETWEEN x AND y", "SELECT expression IS NOT NULL", "SELECT 2 IS DISTINCT FROM NULL, NULL IS NOT DISTINCT FROM NULL", "SELECT 2 < 3, 2 > 3, 2 <= 3, 4 >= NULL, NULL = NULL, 2 <> 2", "SELECT EXISTS(SELECT * FROM grades WHERE course='Math');", "SELECT 'Math' IN (SELECT course FROM grades);", ` SELECT * FROM grades grades_parent WHERE grade= (SELECT MIN(grade) FROM grades WHERE grades.course=grades_parent.course); `, "SELECT MIN(COLUMNS(*)), COUNT(COLUMNS(*)) from numbers;", "SELECT a.* FROM (SELECT {'x':1, 'y':2, 'z':3} as a);", ` WITH ranked_functions as ( SELECT schema_name, function_name, row_number() over (partition by schema_name order by function_name) as function_rank FROM duckdb_functions() ) SELECT * FROM ranked_functions WHERE function_rank < 3; `, ` WITH RECURSIVE tag_hierarchy(id, source, path) AS ( SELECT id, name, [name] AS path FROM tag WHERE subclassof IS NULL UNION ALL SELECT tag.id, tag.name, list_prepend(tag.name, tag_hierarchy.path) FROM tag, tag_hierarchy WHERE tag.subclassof = tag_hierarchy.id ) SELECT path FROM tag_hierarchy WHERE source = 'Oasis'; `, ` select thing[0], thing['hello'], thing[1:3], struct_pack(hello := 'world').hello, `, ` SELECT * FROM range(10) t1 UNION ALL SELECT * FROM range(5) t2; `, ` WITH RECURSIVE per_investor_amount AS ( SELECT 0 AS investors_number, 0.00 AS investment_amount, 0.00 AS individual_amount UNION SELECT investors_number + 1, i.investment_amount, i.investment_amount / (investors_number + 1) FROM investment i, per_investor_amount pia WHERE investors_number << 3 ) SELECT * FROM per_investor_amount ORDER BY investment_amount, investors_number; `, "SELECT row_number() OVER () FROM sales;", "SELECT row_number() OVER (ORDER BY time) FROM sales;", "SELECT row_number() OVER (PARTITION BY region ORDER BY time) FROM sales;", ` SELECT amount - lead(amount) OVER (ORDER BY time), amount - lag(amount) OVER (ORDER BY time), amount / SUM(amount) OVER (PARTITION BY region), FIRST(employee_name) OVER (ORDER BY salary DESC), LAST(employee_name) OVER (ORDER BY salary DESC), NTH_VALUE(employee_name, 2) OVER (ORDER BY salary DESC) FROM basic_pays `, ` SELECT Plant, Date, AVG(MWh) OVER ( PARTITION BY Plant ORDER BY Date ASC RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING) AS 'MWh 7-day Moving Average' FROM 'Generation History' ORDER BY 1, 2 `, "SELECT #1, #2 FROM tbl", "SELECT $hello FROM tbl", "SELECT list_map(x -> x * 2)", ]32:T627,class DuckDBClientCompat extends DuckDBClient { async queryStream(query, params) { const connection = await this._db.connect(); let reader, batch; try { if (params?.length > 0) { const statement = await connection.prepare(query); reader = await statement.send(...params); } else { reader = await connection.send(query); } batch = await reader.next(); if (batch.done) throw new Error("missing first batch"); } catch (error) { await connection.close(); throw error; } // Mosaic utility: convert Arrow value to Javascript value const converters = {} batch.value.schema.fields.forEach(d => { console.log('Type for ', d.name, d.type) converters[d.name] = convertArrowValue(d.type) }) return { schema: getArrowTableSchema(batch.value), async *readRows() { try { while (!batch.done) { let batch_array = batch.value.toArray(); // Convert all values to Javascript version let object_array = [] for (let i = 0; i < batch_array.length; i++) { const d_proxy = batch_array[i]; const d_obj = {} for (let k of Object.keys(converters)) { d_obj[k] = converters[k](d_proxy[k]) } object_array.push(d_obj) } yield object_array; batch = await reader.next(); } } finally { await connection.close(); } } }; } }33:[] 2f:{"id":"2104f685f3965653","slug":null,"title":"DuckDB Parser experiments","thumbnail":"88a480e374118c44e6deccfcb24be91670fce16dc7798a8c359d0f181ffead6a","access_level":"public","create_time":"$D2024-09-13T22:13:03.297Z","update_time":"$D2024-10-10T02:10:52.966Z","view_time":null,"first_public_time":"$D2024-09-13T22:13:03.297Z","fork":{"id":"f5f48bea1ca8e19b","slug":"duckdb-template","title":"DuckDB Template","owner":{"id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González"}},"stars":0,"starred":false,"owner":{"type":"team","id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":"starter_2024"},"version":341,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"2c62f0243f8f45b0","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":null}],"members":[{"type":"team","id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":"starter_2024","role":"editor"},{"type":"individual","id":"2c62f0243f8f45b0","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"DuckDB Parser experiments","cells":[{"id":0,"value":"# DuckDB Parser experiments\n\nFor using DuckDB in Observable, see [this notebook](https://observablehq.com/@jzavala-gonzalez/duckdb-tpc-h) for details.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":44,"value":"select 1 as one","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":257,"value":"viewof query_example_i1 = Inputs.range([1, num_queries], {label: \"Query number\", step: 1, value: 1})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":84,"value":"\n

\n  ${query_script}\n

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":298,"value":"

\n  ${rebuilt_query_script}\n

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":54,"value":"query_script = `select * exclude (wow)`\n// query_script = `select * from range(0, 10)`\n// query_script = query_script_examples[query_example_index]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":74,"value":"query_ast = JSON.parse(query_ast_string)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":262,"value":"query_example_index = query_example_i1 - 1","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":255,"value":"num_queries = query_script_examples.length","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":251,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":110,"value":"\n

\n  \n  ${query_script}\n
\n  \n  ${[...Array(rebuilt_query_length).keys()].map(i => {\n    return htl.html`▼`\n  })}\n  \n  
\n  \n  ${query_token_strings.map((d,i) => {\n    return htl.html`${d}`\n  })}\n  
\n

","pinned":true,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":142,"value":"{\n return htl.html`\n

\n  ${query_token_strings.map((d,i) => {\n    return htl.html`\n    ${i} ->\n    ${d}\n    ->\n    ${query_token_types[i]}\n    
\n`\n  })}\n

\n `\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":190,"value":"{\n // Now do the above but for everything that has a query location\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":310,"value":"rebuilt_query_ast = JSON.parse(rebuilt_query_ast_string)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":274,"value":"query_ast_string = await parse_sql_to_json(query_script)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":308,"value":"rebuilt_query_ast_string = await parse_sql_to_json(rebuilt_query_script)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":281,"value":"rebuilt_query_script = {\n const inner = `select json_deserialize_sql('${query_ast_string}') as sql`\n const res = await client.queryRow(inner)\n return res.sql\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":90,"value":"// query_tokens = await db_instance.tokenize(query_script)\nquery_tokens = await db_instance.tokenize(rebuilt_query_script)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":92,"value":"query_token_strings = {\n const start_offset = query_tokens.offsets;\n const end_offset = [...start_offset.slice(1), undefined]\n\n return start_offset.map((_,i) => rebuilt_query_script.substring(start_offset[i], end_offset[i]))\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":221,"value":"rebuilt_query_length = {\n return _.sum(query_token_strings.map(d => d.length))\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":162,"value":"query_token_types = query_tokens.types.map(d => token_type_enum[d])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":206,"value":"ast_query_location_indices = {\n let data = []\n mapKeysDeep(rebuilt_query_ast, (v,k) => {\n if (k === 'query_location') {\n data.push(v)\n }\n return k\n })\n // data.sort((a,b) => a - b)\n return new Set(data)\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":214,"value":"typeof ast_query_location_indices[0]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":165,"value":"token_type_enum = ['IDENTIFIER', 'NUMERIC_CONSTANT', 'STRING_CONSTANT', 'OPERATOR', 'KEYWORD', 'COMMENT']","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":95,"value":"token_color_scale = d3.scaleOrdinal().range(\n d3.schemeCategory10.map(d => chroma(d).brighten(2))\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":33,"value":"## Parameters","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":35,"value":"viewof duckdb_client = Inputs.radio(['Original', 'Compatibility'], {label: \"DuckDB Client\", value: \"Original\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":38,"value":"viewof duckdb_bundle = Inputs.radio([\"Auto\", 'eh', 'mvp'], {label: \"DuckDB bundle\", value: \"eh\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":56,"value":"## Helper functions\n\nSome include functions from Mause's AST package.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":116,"value":"chroma = require(\"chroma-js@2.4.2\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":58,"value":"function escape_sql(sql) {\n return sql.replaceAll(`\"`, `\"\"`).replaceAll(`'`, `''`)\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":65,"value":"async function parse_sql_to_json(sql) {\n const inner = `select json_serialize_sql('${escape_sql(sql)}') as ast`\n const res = await client.queryRow(inner)\n return res.ast\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":195,"value":"function mapKeysDeep(obj, cb) {\n return _.mapValues(\n _.mapKeys(obj, cb),\n val => (_.isObject(val) ? mapKeysDeep(val, cb) : val),\n )\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":198,"value":"mapKeysDeep({'a': 1, 'b': {'c': 2, 'd': 3}}, (v,k) => k.toUpperCase())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"## Initializing DuckDB\nWe need a version different from the standard in Observable. Took a couple of lessons from [this notebook](https://observablehq.com/@jcolot/duckdb-in-webassembly-with-loadable-extensions). It also loads the TPC-H extension to make experimenting with the queries and answers simpler.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":9,"value":"db_instance.getVersion()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":11,"value":"duckdb = import(\n \"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev232.0/+esm\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":13,"value":"bundle = {\n const bundles = duckdb.getJsDelivrBundles()\n if (duckdb_bundle === 'Auto') {\n return duckdb.selectBundle(bundles)\n } else {\n const bun = bundles[duckdb_bundle]\n bun['pthreadWorker'] = null;\n return bun;\n }\n // return bundles['mvp']\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"async function makeDB() {\n const logger = new duckdb.ConsoleLogger();\n const worker = await duckdb.createWorker(bundle.mainWorker);\n const db = new duckdb.AsyncDuckDB(logger, worker);\n await db.instantiate(bundle.mainModule);\n return db\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"db_instance = makeDB()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"client = {\n const client_class = (duckdb_client === 'Compatibility') ? DuckDBClientCompat : DuckDBClient;\n const c = new client_class(db_instance);\n return c;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":19,"value":"## Modified DuckDBClient\n\nIt just inserts a couple of lines into the original queryStream method","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":21,"value":"$32","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":23,"value":"getArrowTableSchema = observable_stdlib.getArrowTableSchema","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"convertArrowValue = mosaic_core.convertArrowValue","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":27,"value":"observable_stdlib = await import('https://cdn.jsdelivr.net/npm/@observablehq/stdlib@5.8.7/+esm');","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":29,"value":"mosaic_core = await import('https://cdn.jsdelivr.net/npm/@uwdata/mosaic-core@0.9.0/+esm');","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1","titling":"auto"}} 30:"$2f:body"