IBM Cloud Docs
Loading data to Netezza Performance Server

Loading data to Netezza Performance Server

There are different ways in which you can load your data on IBM® Netezza® Performance Server for IBM Cloud Pak® for Data as a Service. Learn how to load data from your local machine or from S3.

Before you begin

  1. Download the client packages from Fix Central and install them as described in Installing client packages.

    One of the easiest ways to upload your data to NPSaaS is by using the nzsql command. nzsql provides an interface that you can use to run SQL commands on the NPSaaS host.

    You cannot use the web console to load data.

  2. Log in to Netezza Performance Server.

    In this example, the the nzsql command is used, but you can use other clients.

    As explained in Connecting to NPSaaS, you can provision NPSaaS with a private endpoint or public and private endpoints. Each endpoint type provides a set of two hostnames that you can connect to NPSaaS.

    To load data, you must be the admin user, or the owner of the database or schema. If you are loading data for the first time and you are not loading data to a preexisting table, you also must have the Create Table privilege. If you need to change user privileges, see Managing users.

    nzsql -host <nps_host_ip> -u <user> -pw <password>
    
    Details
    Input Description
    nps_host_ip

    Specifies the IP address of your instance.
    To retrieve NPS HOST IP:

    1. Log in to your IBM Cloud account.
    2. Go to Private endpoints > Service instance details.
    3. Select your instance.
      Your instance IP address is displayed on the page now.
    user Specifies the user name.
    password Specifies the password for the user.

    Example:

    nzsql -host X.XX.XXX.XXX -u admin -pw password
    Welcome to nzsql, the IBM Netezza SQL interactive terminal.
    Type:  \h for help with SQL commands
           \? for help on internal slash commands
           \g or terminate with semicolon to execute query
           \q to quit
           
    SYSTEM.ADMIN(ADMIN)=> 
    

Loading data from local systems

  1. Prepare the local data file that you want to load:

    1. Note the columns.
    2. Note the location of the file.
    3. Ensure that the data is saved in a delimited file, such as a comma-separated (CSV) file.
  2. Create a table by using the CREATE TABLE AS command.

    The CREATE TABLE AS command creates a table on NPSaaS and fills it with the data from your local data file.

    As a part of this command, your local data file is turned into a transient external table. In other words, your local data file is temporarily treated as a database table that you can query for loading to a NPSaaS table. When you are finished, the transient external table is automatically deleted.

    CREATE TABLE <table> AS SELECT * FROM EXTERNAL <file_path> (<col1>, <col2>, ...) USING (RemoteSource <source_type> DELIM <delimiter_type> SkipRows <number_of_rows>);
    

    Where:

    Details
    Input Description
    table Specifies a name for the table that you are creating.
    file_path Specifies the location of the source data file that you are loading. This source data file is turned into a transient external table.
    col1, col2, ... Specify column names that correspond to column names from the file that you are loading.
    source_type Specifies that the source data file is remote. When you load data by using external tables, by default, the source data file path is assumed to be on the NPSaaS host. If you want to load data from your local machine, you must use the RemoteSource option. For the nzsql client, specify RemoteSource 'NZSQL'. For more information, see RemoteSource option.
    delimiter_type Specifies the delimiter that is used in your source data file. For more information, see Delimiter option.
    number_of_rows Specifies the number of initial rows to skip before loading the data. For more information, see SkipRows option.

    Example:

    CREATE TABLE flight_data AS SELECT * FROM EXTERNAL '/home/user/Downloads/customer_data.csv' (flight_id bigint, passenger_id int, last_name varchar(225), first_name varchar(225), seat_number int) USING (RemoteSource 'nzsql' DELIM ',' SkipRows 1);
    

    For more examples on loading data with external tables, see External tables.

    Your data is loaded into NPSaaS. You can start running queries now.

Loading data from Amazon S3

You can load data to NPSaaS from a data file on Amazon S3 that you previously uploaded there.

  1. Prepare the data that you want to load.

    1. Ensure that the data is on Amazon S3.
    2. Note the file format.
  2. On NPSaaS, create a table by using the CREATE TABLE AS command.

    The CREATE TABLE AS command creates a table on NPSaaS and fills it with the data from S3.

    As a part of this command, your data file is turned into a transient external table. In other words, your data file is temporarily treated as a database table that you can query for loading to a NPSaaS table. When you are finished, the transient external table is automatically deleted.

    CREATE TABLE <table> AS SELECT * FROM EXTERNAL <file> USING (
     REMOTESOURCE <source_type>
     DELIM <delimiter_type>
     UNIQUEID <unique_ID>
     ACCESSKEYID <access_key_ID>
     SECRETACCESSKEY <secret_access_key>
     DEFAULTREGION <default_region>
     BUCKETURL <bucket_URL>
     ENDPOINT <endpoint>
     MULTIPARTSIZEMB <multipart_size> 
     );
    

    Where:

    Details
    Input Description
    source_type Specifies the source type. You must use S3.
    delimiter_type Specifies the delimiter type that is used in your source data file.
    unique_ID Optional. Specifies the namespace that is used to group data in the cloud bucket.
    access_key_ID Specifies the access key.
    secret_access_key Specifies the secret access key.
    default_region Specifies the region in which the bucket is located.
    bucket_URL Specifies the name of the bucket.
    endpoint Specifies the region URL to access your bucket.
    multipart_size Specifies the size of each part in a multipart upload. The default is 105 MB; a maximum 105 MB of buffer can be uploaded in one request.

    Example:

    CREATE TABLE flight_data AS SELECT * FROM EXTERNAL '/customer_data.dat' (flight_id bigint, passenger_id int, last_name varchar(225), first_name varchar(225), seat_number int) USING (
       REMOTESOURCE 'S3'
       DELIM '|'
       UNIQUEID 'sample'
       ACCESSKEYID 'xxxxx'
       SECRETACCESSKEY 'xxxxx'
       DEFAULTREGION 'ap-geo'
       BUCKETURL 'my.sample.bucket'
       ENDPOINT 's3.eu-west-1.cloud-object-storage.appdomain.cloud'
       MULTIPARTSIZEMB '50'
       );
    

Your data is loaded into NPSaaS. You can start running queries now.