CSV File Format

About this task

The following sample data set can be used to complete the tutorial or you can use your own data file and DDL. Download the Boston Property Assessment FY2016 (45.6MB) sample data set (courtesy of Analyze Boston). This package contains a schema (boston_property_assessment_fy2016.schema.sq) file and a data file (BOSTON_PROPERTY_ASSESSMENT_FY2016.cvs).

Procedure

  1. Log in to your Db2 Warehouse SaaS console.
  2. To create a table, complete the following steps:
    1. Copy the contents of (boston_property_assessment_fy2016.schema.sql) into the DDL box under the Run SQL tab.
    2. Specify a schema by concatenating the schema name with the table name separated by a period. For example, <SCHEMA_NAME>.BOSTON_PROPERTY_ASSESSMENT_FY2016. If a schema is not specified, the table is created in your default schema. The default schema name is your user name in uppercase.
    3. Click Run All. The result is a table called BOSTON_PROPERTY_ASSESSMENT_FY2016 in the specified or default schema.

Move your data

Move the data file to the Db2 Warehouse SaaS or Db2 as a Service landing zone. This landing zone stages your CSV file before it's ingested into the database. You need your database credentials. You can get these credentials from the console by clicking Connect in the side navigation bar.

  1. Move the data file to the landing zone of the target database by running the following lift put command:
    % lift put --file <path-to-csv-file>/BOSTON_PROPERTY_ASSESSMENT_FY2016.csv --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>
  2. Load the data set CSV file into the target database by running the following lift load command:
    % lift load --filename BOSTON_PROPERTY_ASSESSMENT_FY2016.csv --target-schema <your-schema-name> --target-table BOSTON_PROPERTY_ASSESSMENT_FY2016 --header-row --remove --file-origin user --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>

    The --header-row option specifies that the first row of the data set contains the column headings. The `--file-origin` user option specifies that this CSV file is user-generated and was not extracted using the lift extract command.

    You're done. You can now run SQL queries on the sample data set from the database console.