tajo-docs/src/main/sphinx/table_management/table_overview.rst

***********************
Overview of Tajo Tables
***********************

========
Overview
========

===========
Tablespaces
===========

Tablespaces is a physical location where files or data objects representing data rows can be stored. Once defined, a tablespace can be referred to by a name when creating a database or a table. Especially, it is very useful when a Tajo cluster instance should use heterogeneous storage systems such as HDFS, MySQL, and Oracle because each tablespace can be specified to use a different storage system. 

Please refer to :doc:`/table_management/tablespaces` if you want to know more information about tablespaces.

=============
Managed Table
=============

``CREATE TABLE`` statement lets you create a table located in the warehouse directory specified by the configuration property ``tajo.warehouse.directory`` or ``${tajo.root}/warehouse`` by default. For example:

.. code-block:: sql

 CREATE TABLE employee (
  id int,
  name text,
  age
 );


==============
External Table
==============

``CREATE EXTERNAL TABLE`` statement lets you create a table located in a specify location so that Tajo does not use a default data warehouse location for the table. External tables are in common used if you already have data generated. LOCATION clause must be required for an external table. 

.. code-block:: sql

 CREATE EXTERNAL TABLE employee (
  id int,
  name text,
  age
 ) LOCATION 'hdfs://table/path';


The location can be a directory located in HDFS, Amazon S3, HBase, or local file system (if a Tajo cluster runs in a single machine). URI examples are as follows:

 * HDFS - ``hdfs://hostname:8020/table1``
 * Amazon S3 - ``s3://bucket-name/table1``
 * local file system - ``file:///dir/table1``
 * Openstack Swift - ``swift://bucket-name/table1``

Table Properties
================
All table formats provide parameters for enabling or disabling features and adjusting physical parameters.
The ``WITH`` clause in the CREATE TABLE statement allows users to set those properties.

The following example is to set a custom field delimiter, NULL character, and compression codec:

.. code-block:: sql

 CREATE TABLE table1 (
  id int,
  name text,
  score float,
  type text
 ) USING TEXT WITH('text.delimiter'='\u0001',
                   'text.null'='\\N',
                   'compression.codec'='org.apache.hadoop.io.compress.SnappyCodec');

Each physical table layout has its own specialized properties. They will be addressed in :doc:`/table_management/data_formats`.


Common Table Properties
=======================

There are some common table properties which are used in most tables.

Compression
-----------

See :doc:`compression`.

Time zone
---------

In Tajo, a table property ``timezone`` allows users to specify a time zone that the table uses for reading or writing. 

You can specify a table time zone as follows:

.. code-block:: sql

   CREATE EXTERNAL TABLE table1 (
    t_timestamp  TIMESTAMP,
    t_date    DATE
   ) USING TEXT WITH('timezone'='ASIA/Seoul') LOCATION '/path-to-table/'
 

In order to learn time zone, please refer to :doc:`/time_zone`.