Redshift

How to Create External Tables in Amazon Redshift?

Amazon Redshift External tables allow you to access files stored in S3 storage as a regular table. You can join the Redshift external table with a database tables such as permanent or temporary table to get required information. You can also perform a complex transformation involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables. There are many situations in which you need to access the…

Continue ReadingHow to Create External Tables in Amazon Redshift?
Comments Off on How to Create External Tables in Amazon Redshift?
March 2, 2022
Azure Synapse

Create External Tables in Azure Synapse Analytics

There are many situations in which you need to access the data without loading it to Azure Synapse analytics. The data may be stored in an external data source such as flat files. Azure Synapse Analytics dedicated SQL pool allows you to query external data available on Azure the data lake or Azure Blob storage. In this article, we will check how to create external tables in Azure Synapse Analytics. Create External Tables in Azure Synapse Analytics Create MASTER KEYCreating a Database Scoped CredentialCreating External Data SourceCreate an External File…

Continue ReadingCreate External Tables in Azure Synapse Analytics
Comments Off on Create External Tables in Azure Synapse Analytics
February 24, 2021
Snowflake

Working with Snowflake External Tables and S3 Examples

Snowflake External tables allow you to access files stored in external stage as a regular table. You can join the Snowflake external table with permanent or managed table to get required information or perform the complex transformations involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables. Snowflake External Tables As mentioned earlier, external tables access the files stored in external stage area such as Amazon…

Continue ReadingWorking with Snowflake External Tables and S3 Examples
Comments Off on Working with Snowflake External Tables and S3 Examples
April 7, 2020
Netezza

Export Netezza Table Data to Linux Local File System

In my other post ‘Netezza Export Table Data to CSV format’, we have seen how to export table to CSV format on a Netezza host machine. In this article, we will check how to export Netezza table data to Linux local file system. We will discuss below topics in this post How to install and configure Netezza ODBC drivers in remote machie Export Netezza table data to Linux local file system using External Tables How to export Netezza table using nzsql command line interface Install Netezza ODBC Drivers In other…

Continue ReadingExport Netezza Table Data to Linux Local File System
Comments Off on Export Netezza Table Data to Linux Local File System
March 6, 2018
Netezza

Netezza External Table DATAOBJECT Option

In this article, we will discuss about the Netezza external table DATAOBJECT and REMOTESOURCE option and how these options work. You must specify these options in case if you are using separate Linux host or an edge node to connect to Netezza data warehouse appliance. Edge Node Background An edge node is something that most of the organisations uses to connect to Netezza server. The edge node will have the all the required scripts to perform the ETL or ELT operations. Normally, edge nodes are installed with JDBC, ODBC or…

Continue ReadingNetezza External Table DATAOBJECT Option
Comments Off on Netezza External Table DATAOBJECT Option
March 23, 2017
Netezza

Netezza Create Table Command and Examples

Use the Netezza CREATE TABLE command to create a new, initially empty table in the current database. The CREATE TABLE command automatically creates a data type that represents the tuple type (structure type) corresponding to one row of the table. Netezza Create Table Command Syntax CREATE [ TEMPORARY | TEMP ] TABLE <table> ( <col> <type> [<col_constraint>][,<col> <type> [<col_constraint>]…] [<table_constraint>[,<table_constraint>… ] ) [ DISTRIBUTE ON { RANDOM | [HASH] (<col>[,<col>…]) } ] [ ORGANIZE ON { (<col>) | NONE } ] [ ROW SECURITY ] Read: Importance of right Distribution…

Continue ReadingNetezza Create Table Command and Examples
Comments Off on Netezza Create Table Command and Examples
November 8, 2016
Netezza

Netezza Backup and Restore: Best Practices

In this post you will learn about Netezza backup and restore data. It provides general information on backup and restore methods available with Netezza system. Netezza Backup and Restore Overview Netezza provides several backup and restore methods as per the different requirements. You can take table or database backup whenever required: You can create full and incremental backups of your various Netezza databases in compressed internal using the nzbackup command and restore them to a Netezza system whenever required using nzrestore command. As a best practice create script that perform…

Continue ReadingNetezza Backup and Restore: Best Practices
Comments Off on Netezza Backup and Restore: Best Practices
October 25, 2016
Netezza

Netezza Export Table Data to CSV format

You can export the Netezza table in many ways. Netezza export table is done using either Netezza external table or Netezza nzsql commands. Use of Netezza external table is fastest way to export Netezza table data compared to a Netezza nzsql command. Netezza nzsql with option -o is easy method compared to creating Netezza external tables. Netezza Export Table using Netezza External Tables You can use the Netezza transient external table to export the data table to flat file (CSV format). This feature is very handy if you are exporting…

Continue ReadingNetezza Export Table Data to CSV format
4 Comments
October 21, 2016
Netezza

Netezza Temporary Tables and Examples

Like any other database, Netezza also supports temp or temporary tables. There are three types of Netezza temporary tables; all of them have advantage over traditional temporary tables. Netezza Temporary Tables Netezza temporary tables are local to the user session. You can use temporary table repeatedly within a user session for multiple times. Like derived tables, temporary tables also use Netezza swap partition space. You can drop and recreate the temporary table within the user session. However, these temporary tables are not visible outside the user session. You do not…

Continue ReadingNetezza Temporary Tables and Examples
Comments Off on Netezza Temporary Tables and Examples
October 19, 2016
Netezza

Netezza Fixed-Width File Loading and Examples

Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left or right alignment. In this format column width are in terms of units of characters. In this post we will learn about Netezza Fixed-Width file loading. Fixed-Width File Overview All data is a series of byte-sequences and has an associated data type, used here as a conceptual or abstract attribute of the data. Fixed-length format files use ordinal positions, which are offsets to identify where fields are within…

Continue ReadingNetezza Fixed-Width File Loading and Examples
Comments Off on Netezza Fixed-Width File Loading and Examples
October 3, 2016