3e:null 3f:["$","title",null,{"children":"DuckDBClient with the duckdb-wasm@1.33.1-dev42.0 | Kimmo Linna | Observable"}] 45:T24a8,// db client similar to the SQLite client: https://observablehq.com/@observablehq/sqlite // Follows the DatabaseClient specification: https://observablehq.com/@observablehq/database-client-specification class DuckDBClientKL { constructor(_db) { this._db = _db; this._counter = 0; } async queryStream(query, params) { const conn = await this.connection(); let result; if (params) { const stmt = await conn.prepare(query); result = await stmt.query(...params); } else { result = await conn.query(query); } // Populate the schema of the results const schema = result.schema.fields.map(({ name, type }) => ({ name, type: getType(String(type)), databaseType: String(type) })); return { schema, async *readRows() { let rows = result.toArray().map((r) => Object.fromEntries(r)); yield rows; } }; } // This function gets called to prepare the `query` parameter of the `queryStream` method queryTag(strings, ...params) { return [strings.join("?"), params]; } escape(name) { return `"${name}"`; } async describeTables() { const conn = await this.connection(); const tables = (await conn.query(`SHOW TABLES`)).toArray(); return tables.map(({ name }) => ({ name })); } async describeColumns({ table } = {}) { const conn = await this.connection(); const columns = (await conn.query(`DESCRIBE ${table}`)).toArray(); return columns.map(({ column_name, column_type }) => { return { name: column_name, type: getType(column_type), databaseType: column_type }; }); } async db() { if (!this._db) { this._db = await makeDB(); await this._db.open({ query: { castTimestampToDate: true } }); } return this._db; } async connection() { if (!this._conn) { const db = await this.db(); this._conn = await db.connect(); } return this._conn; } async reconnect() { if (this._conn) { this._conn.close(); } delete this._conn; } // The `.queryStream` function will supercede this for SQL and Table cells // Keeping this for backwards compatibility async query(query, params) { const key = `Query ${this._counter++}: ${query}`; console.time(key); const conn = await this.connection(); let result; if (params) { const stmt = await conn.prepare(query); result = await stmt.query(...params); } else { result = await conn.query(query); } console.timeEnd(key); return result; } // The `.queryStream` function will supercede this for SQL and Table cells // Keeping this for backwards compatibility async sql(strings, ...args) { // expected to be used like db.sql`select * from table where foo = ${param}` // let queryWithParams = strings.join("?"); // if (typeof args !== 'undefined'){ // for (const param of args) { // queryWithParams = queryWithParams.replace('?', param); // } // } // const results = await this.query(queryWithParams); const results = await this.query(strings.join("?"), args); // return rows as a JavaScript array of objects for now let rows = results.toArray().map(Object.fromEntries); rows.columns = results.schema.fields.map((d) => d.name); return rows; } async table(query, params, opts) { const result = await this.query(query, params); return Inputs.table(result, { layout: "auto", ...(opts || {}) }); } // get the client after the query ran async client(query, params) { await this.query(query, params); return this; } // query a single row async queryRow(query, params) { const key = `Query ${this._counter++}: ${query}`; console.time(key); const conn = await this.connection(); // use send as we can stop iterating after we get the first batch const result = await conn.send(query, params); const batch = (await result.next()).value; console.timeEnd(key); return batch?.get(0); } async explain(query, params) { const row = await this.queryRow(`EXPLAIN ${query}`, params); return element("pre", { className: "observablehq--inspect" }, [ text(row["explain_value"]) ]); } // Describe the database (no arg) or a table async describe(object) { const result = await (object === undefined ? this.query(`SHOW TABLES`) : this.query(`DESCRIBE ${object}`)); return Inputs.table(result); } // Summarize a query result async summarize(query) { const result = await this.query(`SUMMARIZE ${query}`); return Inputs.table(result); } async insertJSON(name, buffer, options) { const db = await this.db(); await db.registerFileBuffer(name, new Uint8Array(buffer)); const conn = await db.connect(); await conn.insertJSONFromPath(name, { name, schema: "main", ...options }); await conn.close(); return this; } async insertCSV(name, buffer, options) { const db = await this.db(); await db.registerFileBuffer(name, new Uint8Array(buffer)); const conn = await db.connect(); await conn.insertCSVFromPath(name, { name, schema: "main", ...options }); await conn.close(); return this; } async insertNativeDB(name,buffer) { const db = await this.db(); await db.registerFileBuffer(name, new Uint8Array(buffer)); const conn = await db.connect(); await conn.query( `ATTACH '${name}' AS ${name} (READ_ONLY);` ) await conn.close(); return this; } async insertParquet(name, buffer) { const db = await this.db(); await db.registerFileBuffer(name, new Uint8Array(buffer)); const conn = await db.connect(); await conn.query( `CREATE VIEW '${name}' AS SELECT * FROM parquet_scan('${name}')` ); await conn.close(); return this; } async insertArrowTable(name, table, options) { const buffer = arrow.tableToIPC(table); return this.insertArrowFromIPCStream(name, buffer, options); } async insertArrowFromIPCStream(name, buffer, options) { const db = await this.db(); const conn = await db.connect(); await conn.insertArrowFromIPCStream(buffer, { name, schema: "main", ...options }); await conn.close(); return this; } // Create a database from FileAttachments static async of(files = []) { const db = await makeDB(); await db.open({ query: { castTimestampToDate: true } }); const toName = (file) => file.name.split(".").slice(0, -1).join(".").replace(/\@.+?/, ""); // remove the "@X" versions Observable adds to file names if (files.constructor.name === "FileAttachment") { files = [[toName(files), files]]; } else if (!Array.isArray(files)) { files = Object.entries(files); } // Add all files to the database. Import JSON and CSV. Create view for Parquet. await Promise.all( files.map(async (entry) => { let file; let name; let options = {}; if (Array.isArray(entry)) { [name, file] = entry; if (file.hasOwnProperty("file")) { ({ file, ...options } = file); } } else if (entry.constructor.name === "FileAttachment") { [name, file] = [toName(entry), entry]; } else if (typeof entry === "object") { ({ file, name, ...options } = entry); name = name ?? toName(file); } else { console.error("Unrecognized entry", entry); } console.log("entry", entry); console.log("file", file); console.log("name", name); console.log("options", options); if (!file.url && Array.isArray(file)) { const data = file; // file = { name: name + ".json" }; // db.registerFileText(`${name}.json`, JSON.stringify(data)); const table = arrow.tableFromJSON(data); const buffer = arrow.tableToIPC(table); const conn = await db.connect(); await conn.insertArrowFromIPCStream(buffer, { name, schema: "main", ...options }); await conn.close(); return; } else { const url = await file.url(); if (url.indexOf("blob:") === 0) { const buffer = await file.arrayBuffer(); await db.registerFileBuffer(file.name, new Uint8Array(buffer)); } else { await db.registerFileURL(file.name, url); } } const conn = await db.connect(); if (file.name.endsWith(".csv")) { await conn.insertCSVFromPath(file.name, { name, schema: "main", ...options }); } else if (file.name.endsWith(".json")) { await conn.insertJSONFromPath(file.name, { name, schema: "main", ...options }); } else if (file.name.endsWith(".parquet")) { await conn.query( `CREATE VIEW '${name}' AS SELECT * FROM parquet_scan('${file.name}')` ); } else { console.warn(`Don't know how to handle file type of ${file.name}`); } await conn.close(); }) ); return new DuckDBClientKL(db); } }46:T632,// See https://duckdb.org/docs/sql/data_types/overview getType = (type) => { const typeLower = type.toLowerCase(); switch (typeLower) { case "bigint": case "hugeint": case "ubigint": return "bigint"; case "double": case "real": case "float": return "number"; case "integer": case "smallint": case "tinyint": case "usmallint": case "uinteger": case "utinyint": return "integer"; case "boolean": return "boolean"; case "date": case "timestamp": return "date"; case "timestamp with time zone": return "date"; case "varchar": case "uuid": return "string"; case "int8": case "long": return "bigint"; case "float8": case "numeric": case "decimal": case "decimal(s, p)": case "float4": case "float32": case "float64": return "number"; case "ubigint": case "utinyint": case "int4": case "int": case "signed": case "int2": case "short": case "int1": case "int64": case "int32": return "integer"; case "bool": case "logical": return "boolean"; case "interval": // date or time delta case "time": case "datetime": case "timestamptz": return "date"; case "char": case "bpchar": case "text": case "string": case "utf8": // this type is unlisted in the `types`, but is returned by the db as `column_type`... return "string"; default: if (/^decimal\(/.test(type)) return "integer"; return "other"; } }47:T6c4,// Adapted from https://observablehq.com/@cmudig/duckdb-client
// Copyright 2021 CMU Data Interaction Group
//
// Redistribution and use in source and binary forms, with or without
// modification, are permitted provided that the following conditions are met:
//
// 1. Redistributions of source code must retain the above copyright notice,
// this list of conditions and the following disclaimer.
//
// 2. Redistributions in binary form must reproduce the above copyright notice,
// this list of conditions and the following disclaimer in the documentation
// and/or other materials provided with the distribution.
//
// 3. Neither the name of the copyright holder nor the names of its contributors
// may be used to endorse or promote products derived from this software
// without specific prior written permission.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
// AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
// ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
// LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
// CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
// SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
// INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
// CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
// ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.48:[] 43:{"id":"6a6eadb91841f4b3","slug":"duckdbclient","title":"DuckDBClient with the duckdb-wasm@1.33.1-dev42.0","thumbnail":"3ee75cb1e8186f39ea79c4b22cd3f018feb5b2626d0754a3230f29f829e3cec0","access_level":"public","create_time":"$D2023-01-26T15:34:16.864Z","update_time":"$D2026-04-11T15:44:40.815Z","view_time":null,"first_public_time":"$D2023-02-13T14:10:19.131Z","has_unpublished_edits":null,"fork":{"id":"357f1a71f976f173","slug":"duckdb","title":"DuckDB in WebAssembly","owner":{"id":"1765d529f9f48b74","login":"cmudig","name":"CMU Data Interaction Group"}},"stars":7,"starred":false,"owner":{"type":"team","id":"b1c4958decb3fa8d","login":"kimmolinna","name":"Kimmo Linna","avatar_url":"https://avatars-next.observableusercontent.com/79ecaae8e9777b4e414cf6c4edb6d6516f9485d73bcaf6816c160d071a3c5aa0","tier":"starter_2024","bio":""},"version":1552,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"aab88ef16f0fb0aa","login":"kimmolinna","name":"Kimmo Linna","avatar_url":"https://avatars-next.observableusercontent.com/79ecaae8e9777b4e414cf6c4edb6d6516f9485d73bcaf6816c160d071a3c5aa0","tier":null,"bio":""}],"members":[{"type":"individual","id":"aab88ef16f0fb0aa","login":"kimmolinna","name":"Kimmo Linna","avatar_url":"https://avatars-next.observableusercontent.com/79ecaae8e9777b4e414cf6c4edb6d6516f9485d73bcaf6816c160d071a3c5aa0","tier":null,"role":"editor"},{"type":"team","id":"b1c4958decb3fa8d","login":"kimmolinna","name":"Kimmo Linna","avatar_url":"https://avatars-next.observableusercontent.com/79ecaae8e9777b4e414cf6c4edb6d6516f9485d73bcaf6816c160d071a3c5aa0","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":4,"collection":null,"type":"notebook","body":{"titling":"auto","title":"DuckDBClient with the duckdb-wasm@1.33.1-dev42.0","cells":[{"id":1121,"value":"# DuckDBClient with the duckdb-wasm@1.33.1-dev42.0","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"duckdb = import(\"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.33.1-dev42.0/+esm\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1414,"value":"arrow = require(\"apache-arrow@latest\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":6,"value":"libraryVersion = duckdb.PACKAGE_VERSION","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":101,"value":"packageName = '@duckdb/duckdb-wasm'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":224,"value":"bundles = duckdb.getJsDelivrBundles()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":247,"value":"bundle = duckdb.selectBundle(bundles)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1292,"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","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":4,"value":"db = makeDB()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":591,"value":"db.getVersion()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":380,"value":"$45","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1011,"value":"$46","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":381,"value":"function element(name, props, children) {\n if (arguments.length === 2) children = props, props = undefined;\n const element = document.createElement(name);\n if (props !== undefined) for (const p in props) element[p] = props[p];\n if (children !== undefined) for (const c of children) element.appendChild(c);\n return element;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":384,"value":"function text(value) {\n return document.createTextNode(value);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":581,"value":"{\n const conn = await db.connect();\n const result = await conn.query(`\tSELECT\n\t\t\tv::INTEGER AS x,\n\t\t\t(sin(v/50.0) * 100 + 100)::INTEGER AS y\n\t\t\tFROM generate_series(0, 100) AS t(v)`);\n await conn.close();\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":833,"value":"## Test the Observable Client","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":839,"value":"client = {\n const c = new DuckDBClientKL();\n await c.queryStream(`CREATE TABLE dt(u STRING, x INTEGER, y FLOAT)`);\n await c.queryStream(\n `INSERT INTO dt VALUES ('a', 1, 5), ('b', 2, 6), ('c', 3, 7), ('d', 4, 8);`\n );\n return c;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1137,"value":"client.db()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":857,"value":"client.table(`SELECT ? as a, ? as b`, [\"2\", \"3\"])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":849,"value":"a = 1","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":847,"value":"client.sql`SELECT ${a}, 2`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":970,"value":"c = client.connection()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":973,"value":"r = await c.send(\"select 1,2\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":984,"value":"r.get(0)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":871,"value":"client.queryRow(`SELECT 1`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":900,"value":"SELECT ${a}, 2","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:client"},{"id":1013,"value":"client","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1177,"value":"buffer = {\n const AWS = await import(\"https://cdn.skypack.dev/@aws-sdk/client-s3@3.722.0\")\n const region = 'fr-par';\n const endpoint = 's3.fr-par.scw.cloud';\n const access_key_id = 'SCWE42AYTDNPNXMMCM4Y';\n const secret_access_key = '4673b800-eda0-4e19-bc30-ada3dbccddf4';\n const client = await new AWS.S3Client({\n region: region,\n buffersize: 100000000,\n s3BucketEndPoint: true,\n endpoint: `https://${endpoint}`,\n credentials:{\n accessKeyId: access_key_id,\n secretAccessKey: secret_access_key}\n });\n const bucket = 'kimmolinna';\n const database_file = \"electricity.db\";\n const file = await client.send(new AWS.GetObjectCommand({Bucket: bucket,Key: database_file}));\n return await file.Body.transformToByteArray()\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1183,"value":" test = {\n const t = await new DuckDBClientKL();\n await t.insertNativeDB('elec',buffer)\n return t\n }","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1447,"value":"select '2024-10-12'::DATE","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:client"},{"id":1194,"value":"from elec.electricity\n","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:test"},{"id":284,"value":"## Test Query\n\nJust to make sure that we can connect to the database. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":263,"value":"{\n const conn = await db.connect();\n const result = await conn.query(`SELECT 1 AS 'Result'\nUNION SELECT 2\nUNION SELECT 3\nORDER BY RESULT\n`);\n await conn.close();\n return Inputs.table(result);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1278,"value":"$47","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"}],"files":"$W48","stdlib":"1"}} 44:"$43:body"