SQL in KDB-X Quickstart
This quickstart walks you through creating, inserting into, querying, and dropping a table in KDB-X using SQL. You will use basic Data Definition Language (DDL) and Data Manipulation Language (DML) commands.
Before you begin
To follow this SQL quickstart, ensure you have the following ready:
- A running KDB‑X session
- Basic familiarity with SQL or q
- (Optional) PyKX installed if you plan to run the PyKX equivalents of the SQL examples
- Access to a KDB‑X console or any IDE (for example, VS Code or Jupyter Notebook) that can run q commands.
This guide shows each operation in SQL, q, and Python (PyKX) to help you understand how the same action maps across interfaces:
- You can run SQL and q statements in the same q console.
- To run the PyKX equivalents, load the PyKX library by running
import pykx as kx.
Start a SQL-enabled session
Before running SQL queries, initialize SQL support in the current process using .s.init[]:
q).s.init[]
1. Create a table
Use CREATE TABLE to define a new table in memory. The example below creates an empty table (tripsFare) with two columns: vendor (string) and fare (float).
Running SQL in q
In KDB-X, SQL statements are executed from the q prompt using the s) SQL execution prompt.
s)CREATE TABLE tripsFare (vendor varchar, fare float)
`tripsFare
// varchar -> symbol, float -> float
tripsFare:([] vendor:`symbol$(); fare:`float$())
tripsFare = kx.schema.builder({
'vendor': kx.SymbolAtom,
'fare': kx.FloatAtom,
})
Case-insensitive identifiers
SQL identifiers are case‑insensitive. Visit the SQL Reference for details.
You can also create a more complex table (cars) with multiple data types and constraints:
// NOTE: Some SQL constraints are not enforced.
// See SQL Reference → CREATE TABLE for details.
s)CREATE TABLE cars (
id int,
Name varchar(250),
Miles_per_Gallon numeric,
Cylinders int,
Displacement real,
Horsepower int,
Weight_in_lbs int NOT NULL,
Acceleration numeric,
Year date NOT NULL,
Origin varchar(60)
);
cars:([]
id:`int$();
Name:`symbol$();
Miles_per_Gallon:`float$();
Cylinders:`int$();
Displacement:`real$();
Horsepower:`int$();
Weight_in_lbs:`int$();
Acceleration:`float$();
Year:`date$();
Origin:`symbol$()
) / make sure there is a whitespace before ')'
cars = kx.Table(data={
'id': kx.IntVector([]),
'Name': kx.SymbolVector([]),
'Miles_per_Gallon': kx.ShortVector([]),
'Cylinders': kx.ShortVector([]),
'Displacement': kx.ShortVector([]),
'Horsepower': kx.ShortVector([]),
'Weight_in_lbs': kx.IntVector([]), # IntVector for larger weights
'Acceleration': kx.FloatVector([]),
'Year': kx.DateVector([]),
'Origin': kx.SymbolVector([]),
})
String columns
Columns declared as varchar map to q symbol. Visit the SQL Reference for details.
2. Insert data
Use INSERT to add rows to an existing table. The following example inserts a single row into the tripsFare table.
/ The column list (vendor, fare) is included for clarity, but it can be omitted only
/ if values are provided for ALL table columns in the correct schema order.
s)INSERT INTO tripsFare(vendor,fare) VALUES ('CMT',100)
// upsert a single row (symbol; float)
`tripsFare insert (`CMT;100f)
tripsFare.insert(['CMT', 100.])
You can also insert multiple rows at once:
s)INSERT INTO tripsFare(vendor,fare) VALUES ('DDS',301),('CMT',589)
`tripsFare insert (`DDS`CMT; 301 589f)
tripsFare.insert([['DDS','CMT'], [301.0, 589.0]])
3. Query data
Use SELECT to retrieve rows. You can return all data or filter it with conditions.
To filter rows with a condition, run:
s)SELECT * FROM tripsFare WHERE fare > 20;
vendor fare
-----------
CMT 100
DDS 301
CMT 589
select from tripsFare where fare > 20
tripsFare.select(where=kx.Column('fare') > 20)
Aggregate data with GROUP BY. The following example calculates the total fare per vendor:
s)SELECT vendor, SUM(fare) AS totalfare FROM tripsFare GROUP BY vendor
vendor totalfare
----------------
CMT 689
DDS 301
q)select totalfare:sum fare by vendor from tripsFare
vendor totalfare
----------------
CMT 689
DDS 301
tripsFare.select(
columns=kx.Column('fare').sum().name('totalfare'),
by=kx.Column('vendor'),
)
vendor| totalfare
------|----------
CMT | 689
DDS | 301
You can also use other aggregate functions, such as COUNT:
s)SELECT COUNT(*) as farenr FROM tripsFare
farenr
-----
3
q)select farenr:count i from tripsFare
farenr
-----
3
tripsFare.select(
columns=kx.Column('i').count().name('farenr')
)
farenr
-----
3
Join tables using a shared column to combine related data. The following example joins tripsFare with cars using a common vendor column.
s)SELECT vendor, fare, Name
FROM tripsFare
JOIN cars ON tripsFare.vendor = cars.Origin
// INNER join equivalent on tripsFare.vendor = cars.Origin
// Make cars keyed on Origin so the join aligns on the key
select vendor, fare, Name
from tripsFare ij (`Origin xkey cars)
tripsFare.merge(cars, left_on='vendor', right_on='Origin', how='inner')[['vendor', 'fare', 'Name']]
About joins
This example uses a simple shared column for clarity. In practice, joins are typically performed on stable identifiers such as IDs or symbols.
Querying on-disk data
SQL in KDB-X queries data stored both in memory and on disk, including memory-mapped historical databases (HDBs).
4. Drop a table
Use DROP TABLE to delete a table from memory. The example below drops the tripsFare table if it exists.
s)DROP TABLE tripsFare
delete tripsFare from `.
del tripsFare
Congratulations! You have now created, populated, queried, and dropped tables in KDB-X using SQL.