Software engineer/entrepreneur, currently working on splitgraph/sgr and/or splitgraph/seafowl
database = new SeafowlDatabase({host: "https://seafowl-test.splitgraph.io"})
database
Plot.plot({
height: 400,
width: 900,
grid: true,
x: {
axis: "top",
label: "Commodity"
},
y: {
label: "Country"
},
color: {
scheme: "PiYG"
},
marks: [
Plot.cell(query, {
x: "commodity",
y: "country_of_production",
fill: "total_volume",
}),
Plot.text(query, {
x: "commodity",
y: "country_of_production",
text: d => d.total_volume?.toFixed(0),
title: "total_volume"
})
]
})
database
Type Table, then Shift-Enter. Ctrl-space for more options.
viewof productionCountry = Inputs.select(productionCountries, {label: "Country of production"})
viewof importCountry = Inputs.select(importCountries, {label: "Country of import"})
viewof commodity = Inputs.select(commodities, {label: "Commodity"})
topExporters = database.sql`SELECT exporter, SUM(volume) AS total_volume FROM supply_chains_copy WHERE country_of_production = ${productionCountry} AND country_of_import = ${importCountry} AND commodity = ${commodity} GROUP BY 1 ORDER BY 2 DESC LIMIT 20`
volumeByYear = database.sql`SELECT year::integer AS year, SUM(volume) AS total_volume FROM supply_chains_copy WHERE country_of_production = ${productionCountry} AND country_of_import = ${importCountry} AND commodity = ${commodity} GROUP BY 1 ORDER BY 1 ASC`
Plot.plot({
marginLeft: 300,
x: {
axis: "top",
grid: true,
},
y: {
domain: topExporters.map(d => d.exporter)
},
marks: [
Plot.barX(topExporters, {
x: "total_volume",
y: "exporter"
})
]
})
Plot.plot({x: {tickFormat: d3.format(",.0f"),}, marks: [Plot.line(volumeByYear, {x: "year", y: "total_volume"})]});
sha256 = require('https://cdnjs.cloudflare.com/ajax/libs/js-sha256/0.9.0/sha256.js')
async function querySeafowl(sql, host = "https://seafowl-test.splitgraph.io") {
let query = sql.trim().replace(/(?:\r\n|\r|\n)/g, " ");
let queryHash = sha256(query);
// Append a random extension (.csv) that Seafowl discards but that makes
// Cloudflare treat the query response as a static cacheable asset.
let response = await fetch(`${host}/q/${queryHash}.csv`, {
headers: {
"X-Seafowl-Query": query
},
mode: "cors",
method: "GET"
});
if (!response.ok) throw new Error(response.status);
let response_t = await response.text();
console.log(response_t);
return response_t ? response_t.trim().split("\n").map(JSON.parse) : [];
}
function quote(q) {
if (typeof q === 'string' || q instanceof String) {
return `'${q.replaceAll("'", "''")}'`;
}
return q;
}
function getObservableType(dfType) {
if (dfType.includes("Utf")) return "string";
if (dfType.includes("Int64")) return "bigint"; // TODO check if this is actually a bigint
if (dfType.includes("Int")) return "integer";
if (dfType.includes("Float")) return "number";
if (dfType.includes("Boolean")) return "boolean";
if (dfType.includes("Date")) return "other"; // We don't deserialize dates from the JSON
return "other";
}
class SeafowlDatabase {
constructor({
endpoint = "https://seafowl-test.splitgraph.io",
}) {
this.endpoint = endpoint;
}
async sql(strings, ...args) {
console.log(strings);
console.log(args);
const result = [strings[0]];
args.forEach((val, i) => {
result.push(quote(val), strings[i + 1]);
});
const interpolatedQuery = result.join("");
console.log(interpolatedQuery);
return await querySeafowl(interpolatedQuery, this.endpoint);
}
async describeTables(schema, database) {
let clauses = [];
let args = [];
let queryFragments = ["SELECT table_catalog AS database, table_schema AS schema, table_name AS name FROM information_schema.tables"];
if (schema) {
args.push(schema);
clauses.push("table_schema = ");
}
if (database) {
args.push(database);
clauses.push("table_catalog = ");
}
if (clauses.length) {
queryFragments[0] += " WHERE " + clauses[0];
clauses.slice(1).forEach((clause) => {
queryFragments.push(" AND " + clause);
});
}
console.log(queryFragments);
return await this.sql(queryFragments, ...args);
}
async describeColumns({
table,
schema = "public",
database = "default"
}) {
console.log(table);
let args = [table, schema, database];
let results = await this.sql`SELECT column_name AS name, data_type AS \"databaseType\", is_nullable AS nullable FROM information_schema.columns WHERE table_name = ${table} AND table_schema = ${schema} AND table_catalog = ${database} ORDER BY ordinal_position ASC`;
// Map databaseType to Observable's type
results.forEach(col => col.type = getObservableType(col.databaseType));
return results;
}
}
// testResult = querySeafowl("SELECT COUNT(*) FROM supply_chains_copy")
testQuoting = database.sql`SELECT ${1}`
testDescribeTables = database.describeTables()
testDescribeTablesInSchema = database.describeTables("public", null)
testDescribeTablesInSchemaAndDb = database.describeTables("public", "default")
testDescribeColumns = database.describeColumns({table: "supply_chains_copy"});
testNonAsciiQueries = querySeafowl("SELECT 1; -- gzip+b85: ABzY8>7Mpt0{^8}QE%EX5Pl}V;>Js1qKt0q3tClCNH->$R3Y88Co2R4QAvz!r!8Ce-)B1^#7PLK<0baxyYqeDosT>Rd<6*2XB;7)V}xcN!{9M!8B=p&4qc=1zGF0e5=WeF5m}%h+z`t9{U?w5E04nKE1aP-ZSRvXB=dl83#jqkz3sRK@D_VCGZ>A*6xbv3NyPCB-=AmPI`$p>aR)aO^RMyqo9UR~IuE9Butrcy58hqnjT=hlvCjh%0i2&hL|{%r%y{6#Vojb|66J}k;}FAm3YP7H^)Q(vcIJEQ3UkI1&GCXz5zdn*AR4g8Iy4Rg#srWZK;jq408_N2B;KH4UWOzj4?)@;&`KI&P6MBz*><O^Uf?Rdt7@)nDGRY~%E~pxLMZA{>TQv?T(;C!{6^!cJGO>ybr}?@4Wih|>i{zyPz;-eOCgAFH&_M88`wcNRYQeWtHZ$N6Q;5{O$FZVnB>Xgu$A<PdY3GdKr8YLTy#|+dMaAR0b9XuObLSpB_Txddd;qZ@D-+zRztu^60)w3Qi%=&I>i`zcNP5gm#Oc9%oI#K6`6DBcDsdS#Tus_v6rgUs49$ZWk@-7O3tMgR?fOLbv8swyF|Phi8*LG2!4@|MH8vrwW>uJ`zr`o?iHRx5DC#YaM8<h6h*<@?<T{gI0cQbj{R^CH^1Pb10Or^sRN%4+ZmZo!B%tMb_bnw;dJF{8t%Wl<`lvuT3LG3iylRnzl~Ey;z(Eb`hAMMwLBzoK^vbZmo1&sAJn}H7TL&wyk7PX(`nrg9Cz%FZA+4>%206Z?Prm8kYw3P;~0&`(NK`JMw;KqoDS7Mp2B1>6%ysokkNB2kJKyUUi+U@5=82ei6efsEs^3*S8nH_1Z<y3p+;4)t?VLU&!ny8UoCiTQZk+LWvIBorQ0ATN%oSRbQO|nG=_tz3E~6B%C`c1xq?e@b5<c2m=gi*foYBO=8<P#rh?|8N&#hH`D`4eojdYa-DJQd7qQxBQ|p%HSeCaqNOE(>XlE*xzgoKTOH|XnTOo)Jcx#Ro-%MqbF)?pl_->D_n%2;EQb}7DsDfSPP`T3mD?$GNnLaw(#0vlb");
productionCountries = database.sql`SELECT DISTINCT(country_of_production) AS c FROM supply_chains_copy ORDER BY 1 ASC`.then(r => r.map(r => r.c));
importCountries = database.sql`SELECT DISTINCT(country_of_import) AS c FROM supply_chains_copy WHERE country_of_production = ${productionCountry} ORDER BY 1 ASC`.then(r => r.map(r => r.c));
commodities = database.sql`SELECT DISTINCT(commodity) AS c FROM supply_chains_copy WHERE country_of_production = ${productionCountry} AND country_of_import = ${importCountry} ORDER BY 1 ASC`.then(r => r.map(r => r.c))
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
