Using Data Manager

Locating the Data File S3 Region, Path, and URL

For information on opening and reviewing items in your S3 instance, see https://docs.aws.amazon.com/AmazonS3/latest/gsg/OpeningAnObject.html

In an S3 bucket, the Region is in the upper-right corner of the screen – US West (N. California) in this case:

Click the file you want to import. To load your S3 file to OmniSci using the steps for S3 Region | Bucket | Path, below, click Copy path to copy to your clipboard the path to your file within your S3 bucket. Alternatively, you can copy the link to your file. The Link in this example is https://s3-us-west-1.amazonaws.com/my-company-bucket/trip_data.7z.

Obtaining Your S3 Access Key and Secret Key

To learn about creating your S3 Access Key and Secret Key, see https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html#Using_CreateAccessKey

If the data you want to copy is publicly available, you do not need to provide an Access Key and Secret Key.

You can import any file you can see using your IAM account with your Access Key and Secret Key.

Your Secret Key is created with your Access Key, and cannot be retrieved afterward. If you lose your Secret Key, you must create a new Access Key and Secret Key.

Loading Your S3 Data to OmniSci

Follow these steps to import your S3 data:

1. Click DATA MANAGER.
2. Click Import Data.
3. Click Import data from Amazon S3.
4. Choose whether to import using the S3 Region | Bucket | Path or a direct full link URL to the file (S3 Link).
   1. To import data using S3 Region | Bucket | Path:
      1. Select your Region from the pop-up menu.
      2. Enter the unique name of your S3 Bucket.
      3. Enter or paste the Path to the file stored in your S3 bucket.
   2. To import data using S3 link:
      1. Copy the Link URL from the file Overview in your S3 bucket.
      2. Paste the link in the Full Link URL field of the OmniSci Table Importer.
5. If the data is publicly available, you can disable the Private Data checkbox. If you are importing Private Data, enter your credentials:
   1. Enable the Private Data checkbox.
   2. Enter your S3 Access Key.
   3. Enter your S3 Secret Key.
6. Choose the appropriate Import Settings. OmniSci supports only delimiter-separated formats such as CSV and TSV.
   1. Null string: If you have substituted a string such as NULL for null values in your upload document, enter that string in the Null String field. The values are treated as null values on upload.
   2. Delimiter Type: Delimiters are detected automatically. You can choose a specific delimiter, such as a comma or pipe.
   3. Includes Header Row: OmniSci tries to infer whether the first row contains headers or data (for example, if the first row has only strings and the rest of the table contains number values, the first row is inferred to be headers). If OmniSci infers incorrectly, you have the option of manually indicating whether or not the first row contains headers.
   4. Quoted String: Indicate whether your string fields are enclosed by quotes. Delimiter characters inside quotes are ignored.
7. Click Import Files.
8. The Table Preview screen presents sample rows of imported data. The importer assigns a data type based on sampling, but you should examine and modify the selections as appropriate. Assign the correct data type to ensure optimal performance. If your column headers contain SQL reserved words, reserved characters (for example, year, /, or #), or spaces, the importer alters the characters to make them safe and notifies you of the changes. You can also change the column labels.
9. Name the table, and click Save Table.

Appending Data to a Table

You can append additional data to an existing table.

To append data to a table:

1. Open Data Manager.
2. Select the table you want to append.
3. Click Append Data.
4. Click Import data from a local file.
5. Either click the plus sign (+) or drag your file(s) for upload. The column names and data types of the files you select must match the existing table. OmniSci supports only delimiter-separated formats such as CSV and TSV. OmniSci supports Latin-1 ASCII format and UTF-8. If you want to load data with another encoding (for example, UTF-16), convert the data to UTF-8 before loading it to OmniSci. In addition to CSV, TSV, and TXT files, you can import compressed delimited files in TAR, ZIP, 7-ZIP, RAR, GZIP, BZIP2, or TGZ format.
6. Click Preview.
7. Click Import Settings
8. Choose Import Settings:
   • Null string: If, instead using a blank for null cells in your upload document, you have substituted strings such as NULL, enter that string in the Null String field. The values are treated as null values on upload.
   • Delimiter Type: Delimiters are detected automatically. You can choose a specific delimiter, such as a comma, tab, or pipe.
   • Quoted String: Indicate whether your string fields are enclosed by quotes. Delimiter characters inside quotes are ignored.
   • Includes Header Row: OmniSci tries to infer whether the first row contains headers or data (for example, if the first row has only strings and the rest of the table contains number values, the first row is inferred to be headers). If OmniSci infers incorrectly, you have the option of manually indicating whether or not the first row contains headers.
   • Replicate Table: If you are importing non-geospatial data to a distributed database with more than one node, select this checkbox to replicate the table to all nodes in the cluster. This effectively adds the PARTITIONS='REPLICATED' option to the create table statement. See Replicated Tables.
9. Close Import Settings.
10. The Data Preview screen presents sample rows of imported data. The importer assigns a data type based on sampling, but you should examine and modify the selections as appropriate. Assign the correct data type to ensure optimal performance.
    
    If your data contains column headers, verify they match the existing headers.
11. Click Import Data.

To append data from AWS, click Append Data, then follow the instructions for Loading S3 Data to OmniSci.

Truncating a Table

Sometimes you might want to remove or replace the data in a table without losing the table definition itself.

To remove all data from a table:

1. Open Data Manager.
2. Select the table you want to truncate.
3. Click Delete All Rows.
4. A very scary red dialog box reminds you that the operation cannot be undone. Click DELETE TABLE ROWS.
   
   Immerse displays the table information with a row count of 0.

Deleting a Table

You can drop a table entirely using Data Manager.

To delete a table:

1. Open Data Manager.
2. Select the table you want to delete.
3. Click DELETE TABLE.
4. A very scary red dialog box reminds you that the operation cannot be undone. Click DELETE TABLE.
   
   Immerse deletes the table and returns you to the Data Manager TABLES list.