Loading data from cloud: Azure Blob Storage

Deployment options: Netezza Performance Server for Cloud Pak for Data SystemNetezza Performance Server for Cloud Pak for Data

You can load data from external tables on cloud into a Netezza Performance Server database table on a Netezza Performance Server host system.

Before you begin

  • If you want the database table to contain only the records from the external table, make sure that the database table is empty before you load the data.
  • If you load from an external table into a database table that is not empty, the load is successful, but the new records are appended to the existing records.

About this task

You can load data from Azure Blob Storage. You can load all data types (including Unicode) and file types (decompressed and compressed formats).

To create an external table, you must be the admin user or have the CREATE EXTERNAL TABLE administration privilege. You must also have permission to read from the path of the data object.

Procedure

  1. Create an external table by issuing the CREATE EXTERNAL TABLE command with the REMOTESOURCE option. 
    CREATE EXTERNAL TABLE TABLE SAMEAS TABLE USING (
DATAOBJECT DATAOBJECT
REMOTESOURCE REMOTESOURCE
DELIM DELIM
UNIQUEID UNIQUEID
AZACCOUNT AZACCOUNT
AZKEY AZKEY
AZREGION AZREGION
AZCONTAINER AZCONTAINER
AZBLOCKSIZEMB AZBLOCKSIZEMB
AZMAXBLOCKS AZMAXBLOCKS
AZLOGLEVEL AZLOGLEVEL 
);
    Example:
    CREATE EXTERNAL TABLE emp_backup SAMEAS emp USING (
DATAOBJECT ('/tmp/emp.dat')
REMOTESOURCE 'AZURE'
DELIM '|'
UNIQUEID 'samplebackup'
AZACCOUNT 'xxxxx'
AZKEY 'xxxxx'
AZREGION 'eastus2'
AZCONTAINER' myContainer'
AZBLOCKSIZEMB '50'
AZMAXBLOCKS '1000'
AZLOGLEVEL 'DEBUG’ 
);
    The DATAOBJECT file specification must refer to a valid file on the cloud platform. For the REMOTESOURCE option, use AZURE.

    If you do not specify a remote source, the system searches for a source file on the appliance host.

    This table lists all the additional parameters, which are necessary if you want to use the REMOTESOURCE AZURE option.

    Option Distribution Description
    UNIQUEID Optional The namespace, which is used to group data in the cloud bucket.
    AZACCOUNT Mandatory The name of the Azure storage account.
    AZKEY Mandatory The Azure key to access your account.
    AZCONTAINER Mandatory The name of the Azure container.
    AZREGION Mandatory on 11.2.2.X

    Not supported on 11.2.1.X

    		 The name of the region of your Azure storage account. For a list of your regions, run the following command:
    az account list-locations --query [].[name] -o table
    AZMAXBLOCKS Optional Indicates the maximum number of blocks, which are allowed in a block blob. The default is 10000.
    AZBLOCKSIZEMB Optional The default is 25; maximum 100. This value is in MB. Indicates the size of a block. Data is written to cloud in blocks. Blocks are appended to form a blob.
    AZLOGLEVEL Optional The default is INFO. This is a log level to control logging verbosity. Other valid values are DEBUG, WARNING, and ERROR.
  2. Insert data from the external table into the table on the Netezza Performance Server host. 
    INSERT INTO TABLE SELECT * FROM TABLE;
    Example:
    INSERT INTO emp SELECT * FROM emp_backup;