The bit.io Developer Hub

Welcome to the bit.io developer hub. You'll find comprehensive guides and documentation to help you start working with bit.io as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    

SQL Query Examples

Example queries and API calls to try.

🚧

Top SQL Tip

One of the top SQL syntax "gotchas" we help our users with is quoting within queries. In Postgres, identifiers like column and table names are enclosed in double-quotes ("column name") while string literals are enclosed in single-quotes ('Hello World').

For example, If we have a text data column named print_statements in a table named logs and wanted to SELECT all records where the value is hello world, we could write a query like this:

SELECT * FROM "username/repo_name"."logs" WHERE "print_statements" = 'hello world'

The double-quotes are not needed unless you are referencing an identifier with upper-case letters, spaces, or special characters (except for underscore). However, it doesn't hurt to include them if it makes querying more clear for you.

The single-quotes are required. If omitted, you will get an error saying that the identifier hello was not found, because Postgres will assume the unquoted text up to the space is an identifier.

If you are getting errors when running a query, it's always a good idea to double-check your quotes. You can read more about this in SQL Identifiers.

Example Queries

Join queries

Join two tables in two different repos

SELECT * FROM "bitdotio/big_mac_index".big_mac_index 
INNER JOIN "bitdotio/economic_freedom_index".world_index 
ON iso_code = iso_a3;
bit -k <your key> query -q 'SELECT * FROM "bitdotio/big_mac_index".big_mac_index 
INNER JOIN "bitdotio/economic_freedom_index".world_index 
ON iso_code = iso_a3;'
API_KEY=<your-api-key>
curl --request POST \
  --url https://api.bit.io/api/v1beta/query/ \
  --header 'Accept: application/json' \
  --header "Authorization: Bearer ${API_KEY}" \
  --header 'Content-Type: application/json' \
  --data '{"query_string": "SELECT * FROM \"bitdotio/big_mac_index\".big_mac_index 
INNER JOIN \"bitdotio/economic_freedom_index\".world_index 
ON iso_code = iso_a3;"}' \ | jq

Compare across countries the number of available hospital beds with the percentage of GDP spent on pharmeceuticals in the year 2012.

SELECT 
 country_name, 
 "indicator_name", 
 n2012, 
 beds_join.indicator, 
 beds_join.value 
FROM 
 "bitdotio/world_bank"."beds" 
INNER JOIN 
 (SELECT "time", location, indicator, value 
  FROM "bitdotio/pharmaceutical_spending"."percent_gdp" 
  WHERE "time"=2012) 
 AS beds_join 
ON country_code=location;
bit -k <your key> query -q 'SELECT 
 country_name, 
 "indicator_name", 
 n2012, 
 beds_join.indicator, 
 beds_join.value 
FROM 
 "bitdotio/world_bank"."beds" 
INNER JOIN 
 (SELECT "time", location, indicator, value 
  FROM "bitdotio/pharmaceutical_spending"."percent_gdp" 
  WHERE "time"=2012) 
 AS beds_join 
ON country_code=location;'
API_KEY=<your-api-key>
curl --request POST \
  --url https://api.bit.io/api/v1beta/query/ \
  --header 'Accept: application/json' \
  --header "Authorization: Bearer ${API_KEY}" \
  --header 'Content-Type: application/json' \
  --data '{"query_string":
"SELECT 
 country_name, 
 \"indicator_name\", 
 n2012, 
 beds_join.indicator, 
 beds_join.value 
FROM 
 \"bitdotio/world_bank\".\"beds\" 
INNER JOIN 
 (SELECT \"time\", location, indicator, value 
  FROM \"bitdotio/pharmaceutical_spending\".\"percent_gdp\" 
  WHERE \"time\"=2012) 
 AS beds_join 
ON country_code=location;
"}' \ | jq

Updated 2 months ago


SQL Query Examples


Example queries and API calls to try.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.