duckdbメモ

await db.registerFileURL("sample1.json", "http://localhost:3000/sample1.json", DuckDBDataProtocol.HTTP, false)

みたいにファイルでできる

terrierscript

insertJSONFromPathを二回かけようとすると、すでにテーブルがあると怒られる。

下記のようにcreate: falseすると通せる

await connection.insertJSONFromPath("sample1.json", {
        name: "fruit",
  })
await connection.insertJSONFromPath("sample2.json", {
    name: "fruit",
    create: false
})

terrierscript

カラム数が違うものはUNION BY ALLは出来ないが、UNION BY NAMEならできる

      const data = await connection.query(`
        SELECT * FROM sample1.json 
        UNION BY NAME
        SELECT * FROM sample2.json
      `)

terrierscript

https://duckdb.org/docs/sql/query_syntax/setops.html#union-all-by-name

terrierscript

キャスト
::INTEGERとかにするとキャスト楽にできる

const data = await connection.query(`
    SELECT SUM(price)::INTEGER FROM sample1.json 
  `)

https://duckdb.org/docs/sql/expressions/cast.html

terrierscript

OPFS

terrierscript

独自の用語とかかと思ったらそうではない

https://developer.mozilla.org/en-US/docs/Web/API/File_System_API/Origin_private_file_system

terrierscript

pivot

https://duckdb.org/docs/sql/statements/pivot.html
ONで指定出来るの楽。

terrierscript

https://duckdb.org/docs/sql/statements/pivot.html#limitations
USINGの変換はコツ必要。外側でやる

terrierscript

vercel: ^1.29.1-dev132.0

terrierscript

await conn.run(`SET home_directory = '/tmp';`)
  await conn.run("INSTALL httpfs")
  await conn.run("LOAD httpfs")

terrierscript

SELECT A FROM foo AS A

とすると、Aがstructでとれる

SELECT A,B FROM foo AS A JOIN baz AS B

みたいなときにめっちゃ便利

terrierscript

csvが意図しないキャストするのはread_csvで制御できそう。
auto_type_candidatesとかはbigintないとかとかしておく方いいかも

terrierscript

auto_type_candidates、そんなに良さそうでもなかった。
結局all_varchar=trueにしてしまったほうが扱いやすいかもしれない

db.openにcastBigIntToDoubleなどもあったが、これらで有益そうなのはなかった

terrierscript

https://github.com/duckdb/duckdb-wasm/issues/1975#issuecomment-3061265665
registerOPFSFileNameするときに、Promiseで待ち発生させないと追いつかなくなるのがある

terrierscript

registerOPFSFileNamehはreadしてくれるわけではないかも

terrierscript

all_varchar=trueで読み込むと下記でレコードを普通のJSONにできる（all_varcharでないとbigintが混ざったりするのでできないケースにぶち当たる）

const parseRecord = (record: arrow.Table<any>) => {
  const records = record.toArray().map(t => t.toJSON())
  return JSON.parse(JSON.stringify(records))
}

terrierscript

       const sample = await conn.query(`SELECT * FROM ${table} LIMIT 1;`)
       console.log(table, JSON.stringify(Object.keys(parseRecord(sample)[0])))

でとれたキーを

const companyKeys = ["name","cd", ...とれたキー] as const

export const CompanySchema = z.record(z.enum(companyKeys), z.string().nullable())

とすると、ざっくり型がとれる

terrierscript

JSON.parse(JSON.stringfify(　だと対応できるのは一段のものだけ。複雑化したり、aggregate functionによっては対応できない

terrierscript

これなら概ねいけそう。

export const parseArrowTable = (record: arrow.Table) => {
  return record.toArray().map(t => {
    return JSON.parse(
      JSON.stringify(t, (_, value) => {
        if (typeof value === "bigint") {
          return Number(value)
        }
        return value
      })
    )
  })
}

terrierscript

      const result = await conn.query(`
        SELECT 
          1 AS v_int,
          1.2::FLOAT AS v_float,
          NULL AS v_null,
          {"a":2, "b":'c', "d":NULL} AS v_struct, 
          [1,2,3] AS v_list,
          ['abc','def'] AS v_str_list,
          map([1, 2], ['a', 'b']) AS v_map,
          DATE '1992-03-27' - INTERVAL 5 DAY AS v_date,
          TIME '12:34' AS v_time,
          'infinity'::DATE AS v_infinity,
          true AS v_boolean,
          { "a": { 
            "b":'c',
            "d": {
              "e": map([1,2],['a','b']) ,
              "f": DATE '1992-03-27' - INTERVAL 5 DAY,
              "g": {
                "h": false
              }
            }
          }} AS v_nested,
        `)

      const r = parseArrowTable(result)

terrierscript

SELECT * FROM read_csv( ... と直接読み出しもできるが、CREATE TABLE xxx FROM とかでテーブル作るのは利点ある

const createTableQuery = (table: string, url: string) => {
  return `CREATE OR REPLACE TABLE ${table} AS SELECT * FROM read_csv('${url}', all_varchar=true);`
}

メリット

console.logあたりにCREATE TABLE文出しておいて、$ duckdb -uiで開いたときに、コピペして環境再現しやすい
テーブル名を先に決めておけるのでクエリ使いまわしやすい
デメリット
使わないテーブルのセットアップが含まれる