30:[] 2e:{"title":"QuestDB","cells":[{"id":0,"value":"# QuestDB\n\n[QuestDB](https://github.com/questdb/questdb) is a high-performance, open-source SQL database for applications in financial services, IoT, machine learning, DevOps and observability. It includes endpoints for PostgreSQL wire protocol, high-throughput schema-agnostic ingestion using InfluxDB Line Protocol, and a REST API for queries, bulk imports, and exports.","pinned":false,"hidden":false,"mode":"md"},{"id":280,"value":"toc()","pinned":false,"hidden":false,"mode":"ojs"},{"id":283,"value":"## QuestDB Client","pinned":false,"hidden":false,"mode":"md"},{"id":17,"value":"We'll use [QuestDB](https://observablehq.com/@observablehq/questdb?collection=@observablehq/database-clients) Observable [DatabaseClient](https://observablehq.com/@observablehq/database-client-specification) implementation to connect to the [QuestDB demo](https://demo.questdb.io/) database instance and run [QuestDB SQL](https://questdb.io/docs/concept/sql-execution-order) queries:","pinned":false,"hidden":false,"mode":"md"},{"id":11,"value":"db = new QuestDatabaseClient(\"https://demo.questdb.io\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":60,"value":"## Database Tables\n\nQuestDB [`SHOW`](https://questdb.io/docs/reference/sql/show) SQL lists database tables and columns:","pinned":false,"hidden":false,"mode":"md"},{"id":13,"value":"SHOW TABLES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":36,"value":"tables = db.sql(['SHOW TABLES;'])","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"## Table Columns\n\n*Select table:*","pinned":false,"hidden":false,"mode":"md"},{"id":30,"value":"viewof table = Inputs.select(tables.map(table => table.table)\n // filter out system tables\n .filter(tableName => !tableName.startsWith('sys'))\n .sort().reverse(), \n {\n label: md`## Table`\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":53,"value":"Inputs.table(tableColumns)","pinned":false,"hidden":false,"mode":"ojs"},{"id":64,"value":"tableColumns = db.sql([`SHOW COLUMNS FROM ${table};`])","pinned":true,"hidden":false,"mode":"ojs"},{"id":69,"value":"## Table Summary\n\nTable summary of the max last ${rowLimit} rows from the `${table}` table using [Summary Table](https://observablehq.com/@observablehq/summary-table) Observable cell:","pinned":false,"hidden":false,"mode":"md"},{"id":152,"value":"viewof rowLimit = Inputs.select([1000, 10000, 50000], {label: 'Row Limit:'})","pinned":false,"hidden":false,"mode":"ojs"},{"id":90,"value":"viewof tableSummary = SummaryTable(data, {label: table})","pinned":true,"hidden":false,"mode":"ojs"},{"id":98,"value":"## Table Data\n\n**${table}**","pinned":false,"hidden":false,"mode":"md"},{"id":103,"value":"viewof searchResults = Inputs.search(data)","pinned":false,"hidden":false,"mode":"ojs"},{"id":105,"value":"Inputs.table(searchResults)","pinned":false,"hidden":false,"mode":"ojs"},{"id":85,"value":"data = db.sql([`SELECT * FROM ${table} LIMIT -${rowLimit}`])","pinned":true,"hidden":false,"mode":"ojs"},{"id":114,"value":"## Data Wrangler\n\nQuery and transform QuestDB `${table}` data with [Arquero](https://uwdata.github.io/arquero/) in Observable [Data Wrangler](https://observablehq.com/@observablehq/data-wrangler) cell with auto-generated JS data transformations code:","pinned":false,"hidden":false,"mode":"md"},{"id":131,"value":"viewof queryEditor = Wrangler(data)","pinned":true,"hidden":false,"mode":"ojs"},{"id":159,"value":"## Table Data SQL\n\nQuery QuestDB tables with Observable [SQL cell](https://observablehq.com/@observablehq/sql-cell):","pinned":false,"hidden":false,"mode":"md"},{"id":162,"value":"SELECT * FROM weather LIMIT -1000","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":268,"value":"**tip:** use [QuestDB SQL](https://questdb.io/docs/concept/sql-execution-order/) docs and tables diagram below to draft your SQL cell queries.","pinned":false,"hidden":false,"mode":"md"},{"id":178,"value":"## Tables Diagram\n\n[Graphviz](https://graphviz.org) tables diagram with table column names and column data types:\n\n**tip**: click on the `tablesDiagram` cell vertical ... menu below to download it in SVG or PNG format.","pinned":false,"hidden":false,"mode":"md"},{"id":191,"value":"tablesDiagram = diagram(db)","pinned":true,"hidden":false,"mode":"ojs"},{"id":243,"value":"### [Mermaid ER Diagram](https://mermaid-js.github.io/mermaid/#/entityRelationshipDiagram)\n\nTODO: get all the tables to show in this mermaid diagram as in the dot diagram above.","pinned":false,"hidden":false,"mode":"md"},{"id":247,"value":"mermaidTablesDiagram = mermaidDiagram(db)","pinned":false,"hidden":false,"mode":"ojs"},{"id":257,"value":"html`\n${DOM.download(await rasterize(mermaidTablesDiagram), `questdb-tables-diagram.png`, \"Save as PNG\")}\n${DOM.download(await serialize(mermaidTablesDiagram), `questdb-tables-diagram.svg`, \"Save as SVG\")}\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":240,"value":"async function mermaidDiagram(db) {\n // get database tables and colulmns\n const tables = await describe(db);\n const tableColumns = await Promise.all(tables.map(table => db.sql([`SHOW COLUMNS FROM ${table};`])));\n\n // create table nodes mermaid diagram string\n const tableNodes = tables.map((table, index) =>\n `${table} {\n ${tableColumns[index].map((column) =>\n `${column.column} ${column.type.replace('(', '-').replace(')', '')}`).join('\\n')}\n }`).join('\\n');\n\n return mermaid`erDiagram\n ${tableNodes}\n `; \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":189,"value":"async function diagram(db) {\n // get database tables and colulmns\n const tables = await describe(db);\n const tableColumns = await Promise.all(tables.map(table => db.sql([`SHOW COLUMNS FROM ${table};`])));\n\n // create table nodes dot diagram string with html table labels\n const tableNodes = tables.map((table, index) =>\n `\"${table}\" [shape=none, label=<\n \n \n \n ${tableColumns[index].map((column) => `\n \n \n \n `).join('')}}\n

\n ${table}\n
${column.column}	${column.type}

\n >]`).join('\\n');\n\n return neato`digraph Tables {\n node [fontsize=12]\n rankdir = LR;\n ${tableNodes}\n }`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":233,"value":"async function describe(db) {\n // get database table names\n return (await db.sql(['SHOW TABLES;'])).map(table => table.table) // table name\n .filter(tableName => !tableName.startsWith('sys')); // skip system tables\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":272,"value":"import {toc} from '@nebrius/indented-toc'","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"import {QuestDatabaseClient} from '@observablehq/questdb'","pinned":false,"hidden":false,"mode":"ojs"},{"id":71,"value":"import {SummaryTable} from '@observablehq/summary-table'","pinned":false,"hidden":false,"mode":"ojs"},{"id":116,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"},{"id":121,"value":" import {Wrangler, op} from '@observablehq/data-wrangler'","pinned":false,"hidden":false,"mode":"ojs"},{"id":250,"value":"import {rasterize, serialize} from '@mbostock/saving-svg'","pinned":false,"hidden":false,"mode":"ojs"},{"id":180,"value":"import {neato} from '@gordonsmith/graphviz'","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}