SQL Server

SQL Server is a relational database management system in which you can store and manage data. SQL Server supports transaction processing, business intelligence, and analytics applications. Through Stream Pipelines, you can load data events directly into the SQL Server tables with the minimal latency.

Stream Pipelines is compatible with SQL Server 2014 and later versions, and supports these SQL Server services:

  • Amazon RDS for SQL Server
  • Azure SQL Database
  • Azure SQL Managed Instance

To establish a connection, ensure that the database endpoint can be accessed publicly through a JDBC connection. In Stream Pipelines, users are authenticated through a database password.

Note: We recommend that you create a new database user for Stream Pipelines and grant the user appropriate read and write permissions.

The database tables are not created automatically by Stream Pipelines. You must create the database tables manually in SQL Server. Ensure that the column names and data types match the properties of the corresponding objects. Additionally, the column names in your tables must match the case sensitivity of the object property names.

To deliver data through Pipelines successfully, refer to this table. This table shows the data types of the Data Catalog object properties and their corresponding SQL Server data types:

Category Data Catalog data type SQL Server data type
Date and Time data types Datetime:
  • Standard
  • American
  • Three-character month
 DATETIME2

DATETIMEOFFSET

DATETIME

SMALLDATETIME
Datetime:
  • Other
 VARCHAR or another string type
Date:
  • Standard
  • American
  • Basic
 DATE
Time:
  • Other
 VARCHAR or another string type
Time:
  • Standard
 TIME
Datetime:
  • Epoch
 BIGINT
Numeric data types Integer:
  • For the value of 1, 0, and NULL
 BIT
Integer:
  • Maximum: Less than 32767
  • Exclusive maximum: Less than 32768
  • Minimum: More than -32767
  • Exclusive minimum: More than -32768
  • Multiple of (number of digits)
 SMALLINT
Integer:
  • Maximum: Between 32767 and 2147483647
  • Exclusive maximum: Between 32768 and 2147483647
  • Minimum: Between -2147483647 and -32767
  • Exclusive minimum: Between -2147483648 and -32768
  • Multiple of (number of digits)
 INT
Integer:
  • Maximum: More than 2147483647
  • Exclusive maximum: More than 2147483648
  • Minimum: Less than -2147483647
  • Exclusive minimum: Less than -2147483648
  • Multiple of (number of digits)
 BIGINT
Number NUMERIC

DECIMAL

FLOAT

REAL
Logical data types Boolean BIT
String VARCHAR - NVARCHAR

CHAR - NCHAR

TEXT - NTEXT
Object VARCHAR or another string type
Note: You can use the data types of the Data Catalog objects with objects that have localized strings and those strings are defined as part of the Data Catalog Locale Selections feature. Stream Pipelines processes only the first value of the object values. For example, if a localized value within a NDJSON record is {"desc": {"en_US":"Blue Car", "de_DE": "Blaues Auto", "es_ES": "Coche Azul"},, then only "Blue Car" as the first value is stored in the table column.