vectorQuerySource

You can use vectorQuerySource to build layers and widgets using custom SQL queries powered by your data warehouse that contain:

• Vector data:

  • points,

  • lines,

  • polygons

  • etc...

Usage

import {vectorQuerySource} from '@carto/api-client';

const data = vectorQuerySource({
  accessToken: 'XXX',
  connectionName: 'carto_dw',
  sqlQuery: `
    SELECT *
    FROM \`carto-demo-data.demo_tables.riskanalysis_railroad_accidents\`
    WHERE year=@selectedYear
  `,
  filters: Filters;
  queryParameters: {'selectedYear': '2015'},
});

Options

type VectorTableSourceOptions = {
  sqlQuery: string;
  spatialDataColumn?: string;
  aggregationExp?: string;
  columns?: string[];
  filters?: Filters;
  filtersLogicalOperator?: 'and' | 'or'; 
  queryParameters?: QueryParameters;
};

• sqlQuery: The custom SQL query that you want to use to retrieve data from your data warehouse. You should use valid syntax for your data warehouse, which allows you to use all kinds of advanced processing directly in the data source. For example, for a BigQuery connection, SELECT * FROM carto-demo-data.demo_tables.chicago_crime_sample LIMIT 100 would be a valid SQL query.

• spatialDataColumn (optional): The name of the column that contains the geospatial information (geometries/geographies) that will be used for visualization and etc. By default, CARTO assumes this column is named geom.

• aggregationExp (optional): An optional SQL expression that will define how this source will group identical geometries found in the spatialDataColumn. By default this property is empty and all rows will be returned. However, when this property is defined:

  • This source will return only one data point for each set of duplicated geometries

  • Each set of duplicated geometries will be aggregated using the aggregation expression defined. For example, if you have a dataset with multiple identical polygons or points and a "income" column and youraggregationExp is AVG(income) , only one polygon or point will be returned, with an income column that equals the average income of all the polygons or points identical to that one.

• columns (optional): The list of columns to retrieve from the query specified in sqlQuery . Useful to increase performance and optimize query costs in queries with a large number of columns, without having to manage all possible variants of sqlQuery. By default, all columns will be retrieved.

• filters (optional): A valid CARTO Filters object, used to perform server-side filtering of this data source with column-based filters.

• filtersLogicalOperator (optional): Indicates whether filters are applied following an AND logic or an OR logic.

• queryParameters (optional): If your sqlQuery contains query parameters, pass the values for each parameter in this field, following your data warehouse mechanism for query parameters, like named or positional parameters.

About queryParameters and parameterized queries

CARTO uses the native mechanisms available in each data warehouse to build parameterized queries, which prevents any SQL injection.

Therefore, to specify the parameters in the query and the expected syntax for queryParameters , you will need to use the specific providers' syntax:

BigQuery

BigQuery uses named parameters.

sqlQuery: 'SELECT * FROM carto-demo-data.demo_tables.retail_stores WHERE storetype = @type AND revenue > @minRevenue'
queryParameters: { 'minRevenue': 100000, 'type': 'Supermarket' },

Please note that empty arrays are not supported as parameter values.

Snowflake

Snowflake supports positional parameters, such as:

sqlQuery: 'SELECT * FROM demo_db.public.import_retail_stores WHERE storetype = :2 AND revenue > :1'
queryParameters: [100000, "Supermarket"],

Redshift and PostgreSQL

Snowflake supports positional parameters, such as:

sqlQuery: 'SELECT * FROM carto_demo_data.demo_tables.retail_stores WHERE storetype = $2 AND revenue > $1...'
queryParameters: [100000, "Supermarket"],

Response

The response of vectorQuerySource is a promise that can be resolved and used in layers and widgets.

Connection compatibility

Connection Data Warehouse	Compatible
BigQuery	✅
Snowflake	✅
Redshift	✅
Databricks	❌ (see Databricks)
PostgreSQL	✅
CARTO Data Warehouse	✅

Layer compatibility

vectorTableSource is compatible with the following layers from the @deck.gl/carto module:

• VectorTileLayer

Widget model compatibility

vectorTableSource is compatible with the following widget models:

• getFormula

• getCategories

• getHistogram

• getRange

• getScatter

• getTimeSeries

• getTable