A SQL database table is a structured dataset with rows and columns. Many vendors provide SQL-style database compatibility, including SQL Server, Oracle, PostgreSQL, and Amazon Redshift. Data contained in these databases can be wide-ranging, including transactional data, prospect or CRM data, or Big Data. This document will explain how to import data from a SQL database table into LityxIQ.
In order to access an external database, you first need to setup a Data Connection in LityxIQ. See https://support.lityxiq.com/277108-Data-Connections for help creating connections.
Whether creating a new raw dataset (https://support.lityxiq.com/319229-Create-New-Dataset) or editing an existing one (https://support.lityxiq.com/261835-Define-the-Source-Settings-for-a-Raw-Dataset), the options that are specific to importing data from database tables are on the Data Source tab and described below. All other tabs are described in more detail here: https://support.lityxiq.com/261835-Define-the-Source-Settings-for-a-Raw-Dataset.
There is also the option to import data using a custom SQL query (see https://support.lityxiq.com/848322-Importing-Data-Using-a-Custom-SQL-Query).
To import data from a specific table, continue with the example below.
Location - This box will show a list of all Data Connections to which you have access in LityxIQ. Select the one containing the connection to the database where the data is located.
Custom SQL - Checking this box will allow you to write a custom SQL query against the chosen database connection. The default setting for this is unchecked, meaning that you will simply choose the data table and fields to import. For more information on the Custom SQL option, see https://support.lityxiq.com/848322-Importing-Data-Using-a-Custom-SQL-Query.
Table - When you click this box, a dialog will open after LityxIQ connects to the database. It will show the various schemas and tables available in the database. Find the one from which you wish to import data and select it.
Fields to Import - Upon selecting the table from which to import data, LityxIQ will determine the fields available in that table and display them in this box. You can use the drop down box to select any number of the fields. Note that if only a subset of the fields in the table are necessary for the analytics you will perform in LityxIQ, it is recommended (and more efficient) to only select those.
Select All - Check this box to always select all fields in the table. This is useful if the fields may change over time, and you always want to import all of them. Note that if the table's fields do change over time and you use the Select All option, you will likely also need to use the Dictionary option "Create Dynamically Each Import" on the Dictionary tab in order for imports to continuously work well.
Row Limit - Enter the number of rows to import. Use 0 to import all rows, the default. This is a way to test settings on a much larger dataset by only importing, say, the first 100000 rows before trying the entire table. Note that for some database types, the subset of rows imported will not necessarily be predictable or in any particular order.
Trim Trailing Spaces - With this box checked, spaces at the end of strings that are imported are automatically removed being stored in the LityxIQ dataset.
Preview Window
See https://support.lityxiq.com/428738-Previewing-a-Raw-Dataset-Source.