By default, Impala uses the text file format with the CREATE TABLE syntax. When data is inserted into this table using INSERT, Ctrl+A (Hex 01) is used as a default delimiter. The default syntax is as follows:

CREATE TABLE users (userID int, username string);

To change the delimiter to, for example, ,, , |, or your_choice, you can use the following syntax:

CREATE TABLE users (userID int, username string) STORED AS textfile FIELDS TERMINATED BY '	';

With the Avro file format, you would have to create tables in Hive first, as shown in the following code snippet:

CREATE TABLE my_avro_table (userID int, userName string)
      ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
       STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
       OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
      TBLPROPERTIES (
          'avro.schema.literal'='{
          "type": "record",
          "name": "user_record",
          "fields": [
             {"name": "userID", "type": "int"},
             {"name": "userName", "type": "string"}
    ]}'),
INSERT OVERWRITE TABLE my_avro_table SELECT *,  "avro"
        FROM functional.alltypes;

Once the file is created in Hive, you can just use it in Impala as any other file as follows:

SELECT * from my_avro_table;

When you create a table with the RCFile format, without using any existing data use with the table, the syntax is as follows:

CREATE TABLE my_rcfile_table (userID int, userName string)
STORED AS RCFile;

Impala can query RCFile-type tables but cannot write to them, so you would need to use Hive to write data into the file using the INSERT statement. With Hive, you don't need to specify the storage file type as Hive takes care of it by default.

Like RCFile, Impala supports the creation of tables that can store SequenceFile data. To create an empty table to store SequenceFile-type data in Impala, you just need to use the following syntax in the Impala shell:

CREATE TABLE my_sequencefile_table (userID int, userName string)
STORED AS SEQUENCEFILE;

The rest of the steps require you to use Hive for setting up file compression and then writing data into the table using the appropriate INSERT statement.

You might be wondering what the Parquet file format is. I would like to provide a little information in this context. The Parquet file format is a column-oriented binary file format that is designed to provide column-specific access to the data. As the data is stored in columns and all columns are stored separately, lookups are happening on columns first. This column-oriented access method makes query processing very fast and efficient, and Impala takes advantage of this file format. Impala provides native support to create, manage, and query tables based on the Parquet file format.

The following is the syntax for creating a table that can store the Parquet file format in Impala:

CREATE TABLE my_parquet_table (userID int, userName string)
     STORED AS PARQUETFILE;

As Impala supports writing the Parquet file format within Impala, you can use the INSERT statement as shown in the following code snippet to write to your Parquet file type from other files:

INSERT OVERWRITE TABLE my_parquet_table
    SELECT * FROM other_table_name;