DDSQL Reference

Overview

DDSQL is SQL for Datadog data. It implements several standard SQL operations, such as SELECT, and allows queries against unstructured data. You can perform actions like getting exactly the data you want by writing your own SELECT statement, or querying tags as if they are standard table columns.

You can run DDSQL queries from AI agents using the Datadog MCP Server ddsql toolset (Preview).

This documentation covers the SQL support available and includes:

• Syntax compatible with PostgreSQL
• Data types
• Type literals
• Arrays
• SQL functions
• Regular expressions
• Window functions
• JSON functions
• Network address functions
• Table functions
• Tags

Syntax

The following SQL syntax is supported:

SELECT (DISTINCT) (DISTINCT: Optional)

Retrieves rows from a database, with DISTINCT filtering out duplicate records.

SELECTDISTINCTcustomer_idFROMorders

JOIN

Combines rows from two or more tables based on a related column between them. Supports FULL JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN.

SELECTorders.order_id,customers.customer_nameFROMordersJOINcustomersONorders.customer_id=customers.customer_id

GROUP BY

Groups rows that have the same values in specified columns into summary rows.

SELECTproduct_id,SUM(quantity)FROMsalesGROUPBYproduct_id

|| (concat)

Concatenates two or more strings together.

SELECTfirst_name||' '||last_nameASfull_nameFROMemployees

WHERE (Includes support for LIKE, IN, ON, OR filters)

Filters records that meet a specified condition.

SELECT*FROMemployeesWHEREdepartment='Sales'ANDnameLIKE'J%'

CASE

Provides conditional logic to return different values based on specified conditions.

SELECTorder_id,CASEWHENquantity>10THEN'Bulk Order'ELSE'Standard Order'ENDASorder_typeFROMorders

WINDOW

Performs a calculation across a set of table rows that are related to the current row.

SELECTtimestamp,service_name,cpu_usage_percent,AVG(cpu_usage_percent)OVER(PARTITIONBYservice_nameORDERBYtimestampROWSBETWEEN2PRECEDINGANDCURRENTROW)ASmoving_avg_cpuFROMcpu_usage_data

IS NULL / IS NOT NULL

Checks if a value is null or not null.

SELECT*FROMordersWHEREdelivery_dateISNULL

LIMIT

Specifies the maximum number of records to return.

SELECT*FROMcustomersLIMIT10

OFFSET

Skips a specified number of records before starting to return records from the query.

SELECT*FROMemployeesOFFSET20

ORDER BY

Sorts the result set of a query by one or more columns. Includes ASC, DESC for sorting order.

SELECT*FROMsalesORDERBYsale_dateDESC

HAVING

Filters records that meet a specified condition after grouping.

SELECTproduct_id,SUM(quantity)FROMsalesGROUPBYproduct_idHAVINGSUM(quantity)>10

IN, ON, OR

Used for specified conditions in queries. Available in WHERE, JOIN clauses.

SELECT*FROMordersWHEREorder_statusIN('Shipped','Pending')

USING

This clause is a shorthand for joins where the join columns have the same name in both tables. It takes a comma-separated list of those columns and creates a separate equality condition for each matching pair. For example, joining T1 and T2 with USING (a, b) is equivalent to ON T1.a = T2.a AND T1.b = T2.b.

SELECTorders.order_id,customers.customer_nameFROMordersJOINcustomersUSING(customer_id)

AS

Renames a column or table with an alias.

SELECTfirst_nameASnameFROMemployees

Arithmetic Operations

Performs basic calculations using operators like +, -, *, /.

SELECTprice,tax,(price*tax)AStotal_costFROMproducts

INTERVAL value unit

Interval representing a time duration specified in a given unit. Supported units:
- milliseconds / millisecond
- seconds / second
- minutes / minute
- hours / hour
- days / day

Data types

DDSQL supports the following data types:

Array types

All data types support array types. See Arrays for array literals, element access, and array functions.

Type literals

DDSQL supports explicit type literals using the syntax [TYPE] [value].

The type prefix can be omitted and the type is automatically inferred from the value. For example, 'hello world' is inferred as VARCHAR, 123 as BIGINT, and true as BOOLEAN. Use explicit type prefixes when values could be ambiguous; for example,TIMESTAMP '2025-01-01' would be inferred as VARCHAR without the prefix.

Example

-- Using type literals in queries
SELECTVARCHAR'Product Name: '||nameASlabeled_name,price*DECIMAL'1.08'ASprice_with_tax,created_at+INTERVAL'7 days'ASexpiry_dateFROMproductsWHEREcreated_at>TIMESTAMP'2025-01-01';

Arrays

Arrays are ordered collections of values that all share the same data type. Every DDSQL base type has a corresponding array type.

Array literals

Use the ARRAY[value1, value2, ...] syntax to construct an array literal. The array’s type is automatically inferred from the values.

SELECTARRAY['apple','banana','cherry']ASfruits;-- VARCHAR array
SELECTARRAY[1,2,3]ASnumbers;-- BIGINT array
SELECTARRAY[true,false,true]ASflags;-- BOOLEAN array
SELECTARRAY[1.1,2.2,3.3]ASdecimals;-- DECIMAL array

Element access

Access individual array elements with a 1-based subscript. Accessing an index that is out of bounds returns NULL.

SELECTARRAY['a','b','c'][1];-- Returns 'a'
SELECTARRAY['a','b','c'][2];-- Returns 'b'
SELECTARRAY['a','b','c'][10];-- Returns NULL (out of bounds)

To access elements of an array column, use the same subscript syntax:

SELECTrecipients[1]ASfirst_recipientFROMemails

Array functions

The following functions operate on arrays:

Functions

The following SQL functions are supported. For Window function, see the separate Window function section in this documentation.

Regular expressions

Flavor

All regular expression (regex) functions use the International Components for Unicode (ICU) flavor:

• Metacharacters
• Operators
• Set Expressions (Character Classes)
• Flag Options for in-pattern flags. Refer to the flags section below for function-level flags.
• Find and Replace (using capture groups)

Functions

Function-level flags

You can use the following flags with regular expression functions:

i

Case-insensitive matching

n or m

Newline-sensitive matching

g

Global; replace all matching substrings rather than only the first one

Window functions

This table provides an overview of the supported window functions. For comprehensive details and examples, see the PostgreSQL documentation.

JSON functions and operators

Network address functions and operators

The inet type represents IPv4 and IPv6 network addresses with an optional CIDR prefix length (for example, 192.168.1.5/24 or ::1). Create inet values with the type literal syntax INET 'value' or by casting a string with CAST(column AS inet).

Functions

Operators

Table functions

Table functions are used to query logs, metrics, cloud costs, and other data sources.

dd.logs(
 columns => array < varchar >,
 filter ? => varchar,
 indexes ? => array < varchar >,
 storage ? => varchar,
 from_timestamp ? => timestamp,
 to_timestamp ? => timestamp
) AS (column_name type [, ...])

SELECTtimestamp,host,service,message,asset_idFROMdd.logs(filter=>'source:java',columns=>ARRAY['timestamp','host','service','message','@asset.id'])AS(timestampTIMESTAMP,hostVARCHAR,serviceVARCHAR,messageVARCHAR,asset_idVARCHAR)

dd.metrics_scalar(
 query varchar,
 reducer varchar [, from_timestamp timestamp, to_timestamp timestamp]
)

SELECT*FROMdd.metrics_scalar('avg:system.cpu.user{*} by {service}','avg',TIMESTAMP'2025-07-10 00:00:00.000-04:00',TIMESTAMP'2025-07-17 00:00:00.000-04:00')ORDERBYvalueDESC;

dd.metrics_timeseries(
 query varchar [, from_timestamp timestamp, to_timestamp timestamp]
)

SELECT*FROMdd.metrics_timeseries('avg:system.cpu.user{*} by {service}',TIMESTAMP'2025-07-10 00:00:00.000-04:00',TIMESTAMP'2025-07-17 00:00:00.000-04:00')ORDERBYtimestamp,service;

dd.cloud_cost_scalar(
 query varchar,
 reducer varchar
 [, from_timestamp timestamp,
 to_timestamp timestamp]
)

SELECT*FROMdd.cloud_cost_scalar('sum:all.cost{*} by {service}','sum',TIMESTAMP'2025-07-10 00:00:00.000-04:00',TIMESTAMP'2025-07-17 00:00:00.000-04:00')ORDERBYvalueDESC;

dd.cloud_cost_timeseries(
 query varchar
 [, from_timestamp timestamp,
 to_timestamp timestamp]
)

SELECT*FROMdd.cloud_cost_timeseries('sum:all.cost{*} by {service}',TIMESTAMP'2025-07-10 00:00:00.000-04:00',TIMESTAMP'2025-07-17 00:00:00.000-04:00')ORDERBYtimestamp,service;

Tags

DDSQL exposes tags as an hstore type, which is inspired by PostgreSQL. You can access the values for specific tag keys using the PostgreSQL arrow operator. For example:

SELECTinstance_type,count(instance_type)FROMaws.ec2_instanceWHEREtags->'region'='us-east-1'-- region is a tag, not a column
GROUPBYinstance_type

Tags are key-value pairs where each key can have zero, one, or multiple tag values corresponding to it. When accessed, the tag value returns a single string, containing all corresponding values. When the data has multiple tag values for the same tag key, they are represented as a sorted, comma-separated string. For example:

SELECTtags->'team',instance_type,architecture,COUNT(*)asinstance_countFROMaws.ec2_instanceWHEREtags->'team'='compute_provisioning,database_ops'GROUPBYtags->'team',instance_type,architectureORDERBYinstance_countDESC

You can also compare tag values as strings or entire tag sets:

SELECT*FROMk8s.daemonsetsdaINNERJOINk8s.deploymentsdeONda.tags=de.tags-- for a specific tag: da.tags->'app' = de.tags->'app'

Additionally, you can extract tag keys and values into individual arrays of text:

SELECTakeys(tags),avals(tags)FROMaws.ec2_instance

HSTORE functions and operators

Writing efficient queries

Queries that read large amounts of data or run heavy computations can be slow or return resource errors. The patterns below are the most common causes, each with a rewrite that usually resolves the issue.

Filter early and aggregate early. Computations in the query filter or a GROUP BY summary run against the index. Computations in a JOIN or a wide LIMIT are held in memory.

Checklist

Before re-running a slow query, ask the following questions:

• Does the query filter include a selective token (not a wildcard like service:* or env:*)?
• Is the time range as small as it can be for the question?
• Are you selecting only the columns you actually use?
• If you have a large LIMIT, could a GROUP BY summary work instead?
• If you have many JOIN clauses, could the query be rewritten as a single scan?
• When joining across sources, is each side filtered?
• Is the JOIN key high-cardinality (user/ request/trace ID)?
• Is the same regex being run more than once per row?

Filtering

Filter on the data source

Always include a selective token in your data source query, such as service:, host:, env:, or any @attribute:value.

Wildcard filters such as service:* or env:* match every event, so they don’t actually narrow the data. Treat these filters as equivalent to leaving the filter blank.

Before

-- No filter; scans all logs in the time range
SELECTtimestamp,service,host,messageFROMdd.logs(columns=>ARRAY['timestamp','service','host','message'],from_timestamp=>NOW()-INTERVAL'7 days',to_timestamp=>NOW())AS(timestampTIMESTAMP,serviceVARCHAR,hostVARCHAR,messageVARCHAR)

After

-- filter => narrows the scan to matching events before any SQL runs
SELECTtimestamp,service,host,messageFROMdd.logs(filter=>'service:checkout-api env:prod',columns=>ARRAY['timestamp','service','host','message'],from_timestamp=>NOW()-INTERVAL'7 days',to_timestamp=>NOW())AS(timestampTIMESTAMP,serviceVARCHAR,hostVARCHAR,messageVARCHAR)

Choose a time range that fits the question

Scan time grows with the time range. Consider starting with a window that covers the question (often a few hours or a day) and widening only when the question demands more. For long-term trends, a pre-aggregated metric or a daily-summary query runs faster than re-scanning raw events on every run.

Goal: understand log volume from the billing service over the last month.

Before

-- Scans 31 days of raw events
SELECTtimestamp,messageFROMdd.logs(filter=>'service:billing',columns=>ARRAY['timestamp','message'],from_timestamp=>NOW()-INTERVAL'31 days',to_timestamp=>NOW())AS(timestampTIMESTAMP,messageVARCHAR)

After

-- Aggregate up front for trends; only fetch the timestamp column needed for grouping
SELECTdate_trunc('day',timestamp)ASday,count(*)ASeventsFROMdd.logs(filter=>'service:billing',columns=>ARRAY['timestamp'],from_timestamp=>NOW()-INTERVAL'7 days',to_timestamp=>NOW())AS(timestampTIMESTAMP)GROUPBY1ORDERBY1;

Column selection

Project only the columns you use

Each column in your query is fetched from storage. Trim the column list to what you use downstream. Wide attributes like the raw message or full HTTP headers can slow down the query significantly.

Before

-- columns array fetches every field, including the expensive raw message
SELECT*FROMdd.logs(filter=>'service:checkout-api',columns=>ARRAY['timestamp','service','host','message','@http.url','@http.status_code'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(timestampTIMESTAMP,serviceVARCHAR,hostVARCHAR,messageVARCHAR,http_urlVARCHAR,status_codeVARCHAR)

After

-- Only declare the columns the analysis actually uses
SELECTtimestamp,service,hostFROMdd.logs(filter=>'service:checkout-api',columns=>ARRAY['timestamp','service','host'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(timestampTIMESTAMP,serviceVARCHAR,hostVARCHAR)

Aggregations

Return summaries, not raw rows

When your goal is to understand the data (such as top-N, counts per category, or distributions), a GROUP BY returns a focused result that is faster to compute and easier to work with than scanning millions of raw rows.

Before

SELECT*FROMdd.logs(filter=>'service:orders-api',columns=>ARRAY['timestamp','service','@http.status_code','message'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(timestampTIMESTAMP,serviceVARCHAR,status_codeVARCHAR,messageVARCHAR)LIMIT5000000;

After

-- Returns ~10 rows (one per status_code), answers the actual question
SELECTstatus_code,count(*)AShitsFROMdd.logs(filter=>'service:orders-api',columns=>ARRAY['@http.status_code'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(status_codeVARCHAR)GROUPBYstatus_codeORDERBYhitsDESCLIMIT100;

Note: Aggregating in SQL with GROUP BY is more efficient than fetching raw rows and aggregating in a downstream step. The engine filters and summarizes data at the source.

Narrow the scan before aggregating on a high-cardinality column

When working with high-cardinality columns (such as emails, IPs, or request IDs):

• Use SELECT DISTINCT or GROUP BY to return one entry per distinct value across workers. Without a tight filter, the result set grows without bound.
• Narrow the data source filter first so the aggregation runs over fewer rows.
• For wide cardinalities, pre-aggregate to one row per time bucket and key, then count distinct across buckets.

Goal: find the distinct user emails in the checkout service’s logs over the last 7 days.

Before

-- No service filter; unbounded distinct set across all logs
SELECTDISTINCTuser_emailFROMdd.logs(columns=>ARRAY['@usr.email'],from_timestamp=>NOW()-INTERVAL'7 days',to_timestamp=>NOW())AS(user_emailVARCHAR)

After

-- Pre-aggregate to one row per (day, email), then count distinct across days
SELECTcount(DISTINCTuser_email)ASdistinct_emailsFROM(SELECTdate_trunc('day',timestamp)ASday,user_emailFROMdd.logs(filter=>'service:checkout-api',columns=>ARRAY['timestamp','@usr.email'],from_timestamp=>NOW()-INTERVAL'7 days',to_timestamp=>NOW())AS(timestampTIMESTAMP,user_emailVARCHAR)GROUPBY1,2)daily;

Joins

Combine self-joins into a single scan

Joining one source to itself many times to correlate different events is one of the most common causes of slow queries. Most self-joins can be rewritten as a single scan with CASE expressions, window functions, or GROUP BY ... HAVING.

Before: 4 self-joins

WITHaAS(SELECTuser_idFROMdd.logs(filter=>'service:checkout-api',columns=>ARRAY['@usr.id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(user_idVARCHAR)),bAS(SELECTuser_idFROMdd.logs(filter=>'service:payment-api',columns=>ARRAY['@usr.id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(user_idVARCHAR)),cAS(SELECTuser_idFROMdd.logs(filter=>'service:shipping-api',columns=>ARRAY['@usr.id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(user_idVARCHAR)),dAS(SELECTuser_idFROMdd.logs(filter=>'service:orders-api',columns=>ARRAY['@usr.id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(user_idVARCHAR))SELECTuser_idFROMaJOINbUSING(user_id)JOINcUSING(user_id)JOINdUSING(user_id);

After: single scan

SELECTuser_idFROMdd.logs(filter=>'service:(checkout-api OR payment-api OR shipping-api OR orders-api)',columns=>ARRAY['service','@usr.id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(serviceVARCHAR,user_idVARCHAR)GROUPBYuser_idHAVINGcount(DISTINCTservice)=4;

Use a high-cardinality join key

When joining, prefer a high-cardinality key like user_id, request_id, or trace_id. Coarse keys like service or status can expand a moderate input into billions of intermediate rows. For large datasets, also narrow the data source filter and time range on each side of the join.

Filter both sides of a cross-source join

When you JOIN across two data sources, apply a selective filter on each side. Examples include logs + RUM, logs + traces, and feed + logs. An unfiltered side becomes a full scan that has to be held in memory for the join. Where possible, pre-aggregate each source separately and join the summaries.

Before

-- No filter on the logs side; full scan held in memory for the join
SELECTlogs.message,rum.user_idFROM(SELECTmessage,trace_idFROMdd.logs(columns=>ARRAY['message','trace_id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(messageVARCHAR,trace_idVARCHAR))logsJOINrumONlogs.trace_id=rum.trace_idWHERErum.view_name='cart';

After

-- Both sides filtered before the join
SELECTlogs.message,rum.user_idFROM(SELECTmessage,trace_idFROMdd.logs(filter=>'service:checkout-api status:error',columns=>ARRAY['message','trace_id'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(messageVARCHAR,trace_idVARCHAR))logsJOINrumONlogs.trace_id=rum.trace_idWHERErum.view_name='cart';

Expressions

Run regular expressions once per row

If you call REGEXP_MATCH once for each output column, the same pattern is evaluated against message repeatedly for every row. Run it once into an array, join the captures into a single delimited string, and unpack them with SPLIT_PART in a downstream SELECT.

Before

-- Same regex evaluated 3 times per row
SELECTSPLIT_PART(ARRAY_TO_STRING(REGEXP_MATCH(message,'user_id=(\S+) latency_ms=(\d+) error=(\S+)'),'|||'),'|||',1)ASuser_id,SPLIT_PART(ARRAY_TO_STRING(REGEXP_MATCH(message,'user_id=(\S+) latency_ms=(\d+) error=(\S+)'),'|||'),'|||',2)ASlatency_ms,SPLIT_PART(ARRAY_TO_STRING(REGEXP_MATCH(message,'user_id=(\S+) latency_ms=(\d+) error=(\S+)'),'|||'),'|||',3)ASerror_codeFROMdd.logs(columns=>ARRAY['message'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(messageVARCHAR);

After

-- Regex runs once per row; captures unpacked in the outer SELECT
SELECTSPLIT_PART(matched,'|||',1)ASuser_id,SPLIT_PART(matched,'|||',2)ASlatency_ms,SPLIT_PART(matched,'|||',3)ASerror_codeFROM(SELECTARRAY_TO_STRING(REGEXP_MATCH(message,'user_id=(\S+) latency_ms=(\d+) error=(\S+)'),'|||')ASmatchedFROMdd.logs(columns=>ARRAY['message'],from_timestamp=>NOW()-INTERVAL'1 day',to_timestamp=>NOW())AS(messageVARCHAR))sub;

Further reading

Request a personalized demo