Insertions

You'll be presented to the SQL insertion syntax, as well as examples of data insertion on SlicingDice for all supported data types.


SQL Syntax

You can execute insertions through SQL using the /sql endpoint on API or through any tool that supports SQL and can connect to SQL drivers. Below you can check the INSERT SQL syntax.

INSERT INTO (dimension)
( column_name [ , ... ] )
VALUES
( { expression | NULL } [ , ... ] )

Insertion Limits

Here is the list of limits and restrictions for insertions on SlicingDice SQL.

Operation Limits
Column Name Reference To reference a single column, insert it in the parameters of the dimension. If a column has a hyphen or is an event column, you need to insert brackets around them. Example: [entity-id], [event.0.value].
Boolean Values Must be True or False
Insertions on Event Columns Insertion on event columns insertions can be made in two ways:
  • Using a raw JSON aggregate value. Example:
    INSERT INTO sales ([entity-id], city, eventcol) VALUES ('ram', 'New York', '[{\'value\': 1, \'date\': \'2017-01-01T00:00:00-05:00\'}, {\'value\': 2, \'date\': \'2017-04-01T00:00:00-04:00\'}]')
  • Broke up into individual columns using the format [eventcolumn.index.date/value]. Example:
    INSERT INTO sales ([entity-id], city, [eventcol.1.value], [eventcol.1.date], [eventcol.0.value], [eventcol.0.date]) VALUES ('ram', 'New York', 2, '04/01/2017', 1, '01/01/2017')
String Values Values for unique-id, string and string-event columns can have at most 1,000 characters and are only stored in lowercase and need to be inside single quotes.
Integer Values Values for integer and integer-event columns must be in the interval from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Decimal Values Values for decimal and decimal-event columns must be in the interval from (-263/10p) to ((263 - 1)/10p) given p decimal place and cannot have more than 20 decimal places.
Decimal separators are done with dots. Ex: 90.923
Date Values Values for date column must be in YYYY-MM-DD format and need to be inside quotation marks.
Datetime Values Values for datetime column must be in YYYY-MM-DDTHH:MM:SSZ format and need to be inside quotation marks.
Event Values Dates in string-event, integer-event, decimal-event and geolocation-event cannot be older than 1990-01-01.
Eventual Consistency The inserted values will be ready to be queried up to 5 seconds after insertion.

Insertion examples

Inserting Strings

Single Value

You can insert only one value for a String latest-value column through a a request like the following:

Entity-id State City
Entity1 CA San Francisco
Entity2 NY New York
INSERT INTO
  default ([entity-id], state, city)
VALUES
  ('Entity1', 'CA', 'San Francisco');
INSERT INTO
  default ([entity-id], state, city)
VALUES
  ('Entity2', 'NY', 'New York');'
Multiple Values

You can insert multiple values for a String list-of-values column through a a request like the following:

Entity-id Page-content
Entity1 welcome
slicingdice
data
warehouse
analytics
database
service
INSERT INTO default ([entity-id], [page-content])
        VALUES ('Entity1', '\''['welcome','slicingdice','data','warehouse','analytics','database','service']'\'');'

Inserting Integers

You can insert only one value for a Integer latest-value column or an array of values for a Integer list-of-values column.

Entity-id Age
Entity1 32
INSERT INTO default ([entity-id], age)
        VALUES ('Entity1', 32);

Inserting Decimals

You can insert only one value for a Decimal latest-value column or an array of values for a Decimal list-of-values column.

Entity-id Weight
Entity1 98.923
INSERT INTO default ([entity-id], weight)
        VALUES ('Entity1', 98.923);

Inserting Booleans

You can insert only one value for a Boolean column.

Entity-id Account-active
Entity1 true
INSERT INTO default ([entity-id], [account-active])
        VALUES ('Entity1', true);

Inserting Dates

You can insert only one value for a Date latest-value column or an array of values for a Date list-of-values column. You should use the ISO8601 format YYYY-MM-DD.

Entity-id Signup-Date
Entity1 2017-04-15
INSERT INTO  default ([entity-id], [signup-date])
        VALUES ('Entity1', '2017-04-15');

Inserting Datetime

You can insert only one value for a Datetime latest-value column or an array of values for a Datetime list-of-values column. You should use the ISO8601 format YYYY-MM-DDTHH:MM:SSZ.

Entity-id Signup-Time
Entity1 2018-05-30T15:22:50Z
INSERT INTO default ([entity-id], [signup-time])
        VALUES ('Entity1', '2018-05-30T15:22:50Z');

Inserting Unique-id

You can insert only one value for an Unique-Id column.

Entity-id Email
Entity1 user1@slicingdice.com
INSERT INTO default ([entity-id], email)
    VALUES ('Entity1', 'user1@slicingdice.com');

Inserting String Events

You can insert these values on String Event columns following the ISO8601 YYYY-MM-DDThh:mm:ssTZ date format for the events. If you omit the date parameter, SlicingDice will select the current timestamp to be used.

Entity-id Page-View
Entity1 /online-demo - 2019-04-15T10:35:23Z
/about - 2019-04-15T10:42:17Z
INSERT INTO
  default (
    [entity-id],
    [page-view.0.value],
    [page-view.0.date],
    [page-view.1.value],
    [page-view.1.date]
  )
VALUES
  (
    'Entity1',
    '/online-demo',
    '2019-04-15T10:35:23Z',
    '/about',
    '2019-04-15T10:42:17Z'
  );

Inserting Integer Events

You can insert these values on Integer Event columns following the ISO8601 YYYY-MM-DDThh:mm:ssTZ date format for the events. If you omit the date parameter, SlicingDice will select the current timestamp to be used.

Entity-id Purchase-Amount
Entity1 129 - 2019-04-15T09:25:19Z
INSERT INTO
  default (
    [entity-id],
    [purchase-amount.0.value],
    [purchase-amount.0.date]
  )
VALUES
  ('Entity1', 129, '2019-04-15T09:25:19Z');

Inserting Decimal Events

You can insert these values on Decimal Event columns following the ISO8601 YYYY-MM-DDThh:mm:ssTZ date format for the events. If you omit the date parameter, SlicingDice will select the current timestamp to be used.

Entity-id Temperature
Entity1 27.943 - 2019-04-15T09:25:19Z
INSERT INTO
  default (
    [entity-id],
    [temperature.0.value],
    [temperature.0.date]
  )
VALUES
  ('Entity1', 27.943, '2019-04-15T09:25:19Z');