BabelDB

Click here to try this code in a live environment.

Query_web( REST/CSV/HTML )

To query JSON/CSV/HTML based tables use query_web and BabelDB will automatically detect the format and return a table:

select * from query_web('https://api3.binance.com/api/v3/ticker/price'); query_web('https://api.github.com/search/repositories?q=more+useful+keyboard') query_web('https://www.iso20022.org/sites/default/files/ISO10383_MIC/ISO10383_MIC.csv') query_web('https://en.wikipedia.org/wiki/List_of_bank_failures_in_the_United_States_(2008%E2%80%93present)') https://fred.stlouisfed.org/graph/fredgraph.csv?id=MORTGAGE30US

query_web takes the following arguments:

Arg	Description
HTTP	HTTP endpoint with data
JQ Path (Optional)	JQ Path Expression
ColumnTypes (Optional)	A json struct that specifies the key names and value types contained within the JSON file e.g. ``{key1: 'INTEGER', key2: 'VARCHAR'}`` - If not specified these will be guessed.

Query_db(db,) - join data from different databases

Add a mysql / postgresql connection and query them. Notice the SQL queries can be wrapped in parentheses (), Quotes also work but parentheses allow SQL highlighting to work better in most editors.

Comparing the U.S. Unemployment rate to U.S. National Home Price Index:

select a.date, CSUSHPINSA/63.74 AS CSUSHPINSA,b.UNRATE/6.60 AS UNRATE FROM query_db('FRED','CSUSHPINSA') a LEFT JOIN query_db('FRED','UNRATE') b ON a.date=b.date

Query_data( hardcoded JSON/CSV )

query_data("[{d:'2011-01-01', v:9},{d:'2011-01-02', v:6},{d:'2011-01-03', v:8},{d:'2011-01-04', v:4}]")
query_data('Date,val 2001-01-07,88 2001-01-08,99 2001-01-09,55 2001-01-10,66 2001-01-11,70 2001-01-12,75 ')

FAQS

Why do this?

TimeStored make tools for data analysts and SQL users:

• qStudio is an SQL IDE for data analysts
• PulseUI is a low-code tool for developing real-time internal applications fast.

BabelJDBC allows PulseUI to combine queries from any data source in a clean way without clicking and dragging UI elements. We are particularly proud that it just is SQL with special functions added in.

Hows does it work?

query_ queries cause temporary tables to be created and the function call is replaced with that table name. Simplified example: select date,txt from query_web('https://api3.binance.com/api/v3/ticker/price'); becomes:

CREATE TEMPORARY TABLE temp.price( d DATE, col INTEGER, txt VARCHAR);
	INSERT INTO TABLE temp.price VALUES( ......);
	INSERT INTO TABLE temp.price VALUES( ......);

	select date,txt from temp.price;
	

Limitations / TODO

• Babel only supports limited simple column types. Would be nice to add arrays etc.
• Babel uses H2 version 1.4.200 as SQL engine, Pluggable option would allow user to choose.

Why Babel?

From The Hitchhiker's Guide to the Galaxy , The Babel fish is a small, bright yellow fish, which can be placed in someone's ear in order for them to be able to hear any language translated into their first language. Ford Prefect puts one in Arthur Dent's ear at the beginning of the story so that he can hear the Vogon speech.

BabelJDBC allows combining queries between any JDBC compatible database.

Huge credit and thanks goes to:

• Charles Skelton for the initial idea and name.
• H2 Database for supplying the core engine that performs the SQL.