2c:null 2d:["$","title",null,{"children":"Create 7M row Lookup Table from local Parquet file & DuckDB client | katirg | Observable"}] 32:T483,# Create 7M row Lookup Table from local Parquet file & DuckDB client This notebook follows: https://observablehq.com/@bmschmidt/duckdb-degrees, where data `degrees` is a `5,286,850` (actually looks like it's `7,631,696`) central degrees table that includes numeric codes identifying majors and schools for every school in the US. Info on DuckBD: https://www.datacamp.com/blog/an-introduction-to-duckdb-what-is-it-and-why-should-you-use-it ``` DuckDB is a free, open-source, embedded, in-process, relational, OnLine Analytical Processing (OLAP) DataBase Management System (DBMS). in-process: runs from within the application instead of an external process the application connects to OLAP: OLAP databases are commonly oriented by columns, which means data is organized by columns. All data associated with a column is stored near one another in memory, and the database is optimised for reading and computing on the columns efficiently. A columnar-vectorized query engine helps to make efficient use of the CPU cache and speed up response times for analytical query workloads. ``` **NB**: Use `import {DuckDBClient} from '@cmudig/duckdb'`33:T47b,numDegrees_timeSeries = { // if (selected_schools.length !== 1) { // return html`

If you select a single school, all its majors should show here.`; // } if (selected_schools.length == 1) { const majors = await client.query( `SELECT CIPCODE, Discipline, year, sum(degrees) as degrees FROM "degrees" WHERE UNITID = ${selected_schools[0].UNITID} AND ${combine_filters([ ["gender", gender_filter] ])} GROUP BY year, CIPCODE, Discipline` ); return Plot.plot({ height: 2.5 * majors.toArray().length, //2000 marginLeft: 260, x: {label: "Year", tickFormat: "d" // ensure year is displayed with no comma }, marks: [ Plot.dot(majors, { x: (d) => d.year, y: (d) => d.CIPCODE + "(" + d.Discipline + ")", fill: "degrees", stroke: "gray", strokeWidth: 0.5, r: (d) => d.degrees, title: (d) => `${d.Discipline}\nYear: ${d.year}\nDegrees: ${d.degrees}` // Tooltip }) ] }); } else { return html`

If you select a single school, all its majors should show here.`; } }34:T16bb,// Modified from original rangeInput fn to include a text label // uses class="ds_font_family" for label function myrangeInput(options = {}) { const { min = 0, max = 100, step = "any", value: defaultValue = [min, max], color, width, theme = theme_Flat, enableTextInput = false, label // <--- Add this line } = options; // ... (rest of your existing code) const controls = {}; const scope = randomScope(); const clamp = (a, b, v) => (v < a ? a : v > b ? b : v); const html = htl.html; const inputMin = html``; inputMin.style = "width:5em"; const inputMax = html``; inputMax.style = "width:5em"; // Will be used to sanitize values while avoiding floating point issues. const input = html``; const dom = html`${ label ? html`${label} ` : "" // <--- Add this line to render the label }${ enableTextInput ? inputMin : "" }
${(controls.track = html`
${(controls.zone = html`
${(controls.range = html`
${(controls.min = html`
`)} ${(controls.max = html`
`)} `)} `)} `)} ${html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":452,"value":"## II. Read local Parquet file with parquet-wasm","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":421,"value":"tripdata = FileAttachment(\"yellow_tripdata_2024-07-first-2m.parquet\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":424,"value":"tripdata_arrowTable = {\n const parquetBytes = new Uint8Array(\n await tripdata.arrayBuffer()\n );\n // Set large batchSize to ensure input data stays as a single Arrow RecordBatch\n const wasmTable = readParquet(parquetBytes, {batchSize: Math.pow(2, 31)});\n const arrowTable = arrow.tableFromIPC(wasmTable.intoIPCStream());\n return arrowTable;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":398,"value":"// viewof factedSearch = FacetedSearch(db_getRemoteFile, { filter: duckDBFilter })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":431,"value":"tripdata_arrowTable.numRows // 2,000,000","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":434,"value":"Inputs.table(tripdata_arrowTable)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":444,"value":"tripdata_arrowTable.filter( arrowES5.predicate.col('passenger_count').eq(10) )","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":442,"value":"Arquero table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":436,"value":"aq.fromArrow(tripdata_arrowTable)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":33,"value":"# Filters","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":23,"value":"// viewof geoType = Inputs.form({\n// metric: Inputs.select(\n// [\"househumber\",\"interpolation\",\"locality\",\"municipality\", \"street\",\"village\"],\n// { label: \"Choose a geo type\" }\n// )\n// })\n\nviewof geoType = Inputs.select([\"househumber\",\"interpolation\",\"locality\",\"municipality\", \"street\",\"village\"], \n {value: \"municipality\", label: \"Choose a geo type\", multiple: true})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":41,"value":"geoType","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":184,"value":"geoType[0]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":35,"value":"viewof address = Inputs.text(\n {label: \"Address\", placeholder: \"Enter an address\", value: \"123 Street\"}\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":8,"value":"--SELECT code_commune_ref\n-- , SUM(nb_adresses)::INT n\n-- , COUNT()::INT c\nSELECT \nFROM read_parquet('https://static.data.gouv.fr/resources/bureaux-de-vote-et-adresses-de-leurs-electeurs/20230626-135723/table-adresses-reu.parquet')\n\n--WHERE geo_type IN ('municipality')\nLIMIT 10","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":56,"value":"## Imports","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":464,"value":"import {DuckDBClient} from '@cmudig/duckdb'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":374,"value":"// https://observablehq.com/@kasivisu4/duckdb-filter\nimport {duckDBFilter} from '@kasivisu4/duckdb-filter'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":438,"value":"import {aq, op} from \"@uwdata/arquero\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":218,"value":"import {getDataTable} from '@huggingface/parquet-quick-view'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":393,"value":"// https://observablehq.com/@kasivisu4/duckdb-filter\nimport { FacetedSearch } from \"@kasivisu4/faceted-search-filter\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":426,"value":"// from: https://github.com/kylebarron/parquet-wasm\n// Load the parquet-wasm library\nreadParquet = {\n const parquetModule = await import(\n \"https://unpkg.com/parquet-wasm@0.6.0/esm/parquet_wasm.js\"\n );\n // Need to await the default export first to initialize the WebAssembly code\n await parquetModule.default();\n return parquetModule.readParquet;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1492,"value":"_Various imports for range slider variations:_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1254,"value":"import {rangeInput} from '@kasivisu4/range-slider'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1437,"value":"import {interval} from '@mootari/range-slider'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1462,"value":"import {theme_Flat} from '@mootari/range-slider'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1460,"value":"cssLength = v => v == null ? null : typeof v === 'number' ? `${v}px` : `${v}`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1465,"value":"function randomScope(prefix = 'scope-') {\n return prefix + (performance.now() + Math.random()).toString(32).replace('.', '-');\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1457,"value":"$34","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":728,"value":"import { SummaryTable } from \"@observablehq/summary-table\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":371,"value":"## Requires","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":70,"value":"arrow = require('apache-arrow')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":446,"value":"arrowES5 = require('@apache-arrow/es5-umd@0.15.1')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1587,"value":"## Tests","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1593,"value":"### T1. Display data in Observable data table cell or SQL cell\nResults: Good, but there are embedding issues for the data table cell. The filter UI section cannot be exported so means that the best feature of the data table cell cannot be used. The SQL cell table is too slow for loading all rows and does not have pagination.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":607,"value":"SELECT *\nFROM degrees\nWHERE INSTNM ILIKE '%trucking%'\n--LIMIT 111","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":90,"value":"## Refs","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":92,"value":"### Notebooks\n\n- [Read remote parquet files using DuckDB](https://observablehq.com/@monitus/get-remote-parquet-files-directly)\n- [Read remote parquet files using parquetWasm](https://observablehq.com/@huggingface/parquet-quick-view) ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"}],"files":"$W35","stdlib":"1","titling":"auto"}} 31:"$30:body"