Tables & Queries
How to access in-memory and persistent data tables.
Database Access
To connect to and run queries on your persistent data store, you'll need to define a connection using the Storage
event, like so:
=Storage("database")
To query, simply point a Query event to this storage, and the storage event will return the results.
If the result is rows, you'll receive a list of rows. If the result is anything else, you'll receive a message, such as 11 rows effected.
Postgres-compatibility & SQL nuances
Summit uses CockroachDB for its persistent data storage due to its incredible durability, and SQLite to filter in-memory tables due to its ephemeral speed. While the vast majority of SQL syntax is supported by both, there can be tiny differences, and your
Query
syntax should take this into account. If you're asking GPT to write your SQL, just tell them Cockroach Labs or SQLite sent you. ;-)
Table
A lot of business processes involve the usage of data stored in a remotely-accessible CSV file. These files are often "published" spreadsheets (using a service like Google Sheets or Microsoft Excel) or even a read-only database view or "clip."
When you want to pull this data into SEL, you can use Table
to fetch and structure the data, and Query
to run ad hoc queries on it.
To pull a remote CSV file accessible by URL into a Summit, you can use:
=Table('https://docs.google.com/...'<csv_url>)
This will spin up a small database inside your model and store the table uniquely inside of it. In other words, you are welcome to use this event in multiple places, and the data will be stored in different tables:
=Table('https://data.heroku.com/clips/...'<data_url>)
In the SQL of downstream Query
events, you will refer to this table as simple $table
. For example: SELECT count(*) FROM $table
.
You may also wish to pass Table
a list of dictionaries, such as the output of a Parser
event. You can do this by leaving the Table
argument empty:
=Table()
In either case, you may provide an optional argument for the name of the table:
=Table("contacts")
Or
=Table("https://sheets.google.com/...", "contacts")
This will load the input data (or the data from the remote CSV) into a table in your model called contacts
.
This can be useful for writing a query in an event that is not directly downstream of the table.
In-Memory SQL is SQLite
Summit uses in-memory SQLite databases for
Table
, so all of yourQuery
syntax should observe what's included in theSQLite
dialect when working withTable
events.Learn more about SQLite's syntax and function library.
Query
Once you have a data file stored inside a Table
or Storage
, you'll most likely want to run a query on it. In SEL, we can do this by passing SQL to an event like so:
=Query("SELECT min(price) FROM $table WHERE status = 'ACTIVE'")
An example using Table
, Query
and Response Data.
#!/usesummit/sel/0.1a
"csv_table": =Table("https://docs.google.com/spreadsheets/d/e/2PACX-1vRg7CfZbL3V_h4fNzgl8KqTO7JKSuJ1NqJ03C3i4P6eK12jSDbOGPvTBPhzJMoQmkVEBLYUl2r5v5Sf/pub?gid=0&single=true&output=csv")
"csv_rows": =Query("SELECT * FROM $table LIMIT 2")
"my_csv_response": =Response("my_csv_data")
"csv_table" -> "csv_rows" -> "my_csv_response"
Multiline SQL
You can also write multi-line SQL queries by wrapping your query in """
:
#!/usesummit/sel/0.1a
"select_images": =Query("""
SELECT * FROM media_store
""")
"db": =Storage("database")
"rows": =Response("output")
"select_images" -> "db" -> "rows"
Updated 27 days ago