SQL interface (alpha)

Introduction

In addition to HTTP REST APIs, Bloomeo provides a SQL interface. The SQL API enables delivering data over the Postgres-compatible protocol to a wide range of data visualization tools. In general, if an application connects to PostgreSQL database, it can connect to this interface as well. This feature is currently in alpha and may be subject to changes.

Prerequisites

To use the SQL interface, you need:

  • A Bloomeo account with access to the SQL interface feature.
  • Basic knowledge of SQL syntax and commands.
  • An up-to-date JWT token for authentication (see Authentication for more details).

Make your first query

To execute SQL queries against the Bloomeo SQL interface, follow these steps:

  • Get a JWT token for authentication.

  • Connect to the SQL interface using a PostgreSQL client (e.g., psql, DBeaver, etc.) with the following connection parameters:

    • Host: cubesql.<ENV>.bloomeo-app.com (replace <ENV> with your environment you can find in your current url bar, e.g., in developer.app.bloomeo-app.com, the ENV is app).
    • Port: 15432
    • User: cube
    • Password: Your JWT token
    • Database: cube

  • Once connected, you can execute SQL queries. For example, to retrieve all tables in the database, you can run:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
  • Or get the number of trials you have access to:
SELECT COUNT(*) FROM trial WHERE year = 2024;

Examples

With psql

Here is an example of how to connect to the Bloomeo SQL interface using the psql command-line tool:

export PGPASSWORD="your_jwt_token_here"
psql -h cubesql.demo.bloomeo-app.com -U cube -d cube -p 15432
psql (15.10 (Debian 15.10-0+deb12u1), server 14.2 (Cube SQL))
Type "help" for help.

cube=> SELECT COUNT(*) FROM trial WHERE year = 2024;
 COUNT(UInt8(1)) 
-----------------
               5
(1 row)

With R

  1. Install dependencies:
install.packages(c("RPostgres", "DBI"))
  1. Use the following code to connect and query:
library(DBI)             

# Set env and credentials
bloomeo_env <- "" # replace "" with the environment tag you can find in your current url bar, e.g., in developer.app.bloomeo-app.com, the ENV is app
token <- "" # replace "" with your JWT token

# Init a DB connector
con <- dbConnect(
  RPostgres::Postgres(),
  host = paste0("cubesql.",bloomeo_env, ".bloomeo-app.com"),  
  port = 15432,                   
  dbname = "cube",
  user = "cube",
  password = token
)

# Simple query
res <- dbGetQuery(con, "
  SELECT COUNT(*)
  FROM trial
  WHERE year = 2024;
")
print(res)

# Disconnect
dbDisconnect(con)

With Python

  1. Install dependencies:
# Install via pip
pip install psycopg2-binary sqlalchemy
# Install via uv
uv add psycopg2-binary sqlalchemy
  1. Use the following code to connect and query:
from sqlalchemy import create_engine, text

# Set credentials and connection URL in SQLAlchemy format
user = "cube"
token = "" # replace "" with your JWT token
bloomeo_env = "" # replace "" with the environment tag you can find in your current url bar, e.g., in developer.app.bloomeo-app.com, the ENV is app
host = f"cubesql.{bloomeo_env}.bloomeo-app.com"

# Set URL
port = "15432"
db_name = "cube"
db_url = f"postgresql+psycopg2://{user}:{token}@{host}:{port}/{db_name}"

# Set query
query = """
  SELECT COUNT(*)
  FROM trial
  WHERE year = 2024;
"""

# Start engine and connect to DB
engine = create_engine(db_url)

# Query the DB
with engine.connect() as conn:
    result = conn.execute(text(query))
    for row in result:
        print(row)

Limitations

  • The SQL interface is currently in alpha and may have limitations or bugs.
  • Not all SQL features may be supported, refer to the Reference documentation for details.
  • Performance may vary depending on the complexity of the queries and the load on the system.
  • Ensure to keep your JWT token secure, as it provides access to your data.