.. _s3:

******************************
Amazon Web Services
******************************

SQream uses a native S3 connector for ingesting data. File names may contain wildcard characters, and the files can be in CSV or columnar format, such as Parquet and ORC.
   
S3 Configuration
================

The best practice for granting access to AWS S3 is to create an `Identity and Access Management (IAM) <https://docs.aws.amazon.com/IAM/latest/UserGuide/getting-started.html>`_ user account. If creating an IAM user account is not possible, you may follow AWS guidelines for `using the global configuration object <https://docs.aws.amazon.com/sdk-for-javascript/v2/developer-guide/global-config-object.html>`_ and setting an `AWS region <https://docs.aws.amazon.com/sdk-for-javascript/v2/developer-guide/setting-region.html>`_

S3 URI
======

When you want to access data in an S3 bucket (using :ref:`copy_from` or loading to``external_tables``), you must specify the S3 URI with the file path.

The following is an example of the general S3 syntax:

.. code-block:: console
 
   s3://bucket_name/path

Authentication
==============

SQream supports ``AWS ID`` and ``AWS SECRET`` authentication. These should be specified when executing a statement.

Examples
========

Creating a Foreign Table
------------------------

Based on the source file's structure, you can create a foreign table with the appropriate structure, and point it to your file as shown in the following example:

.. code-block:: sql
   
   CREATE FOREIGN TABLE nba
   (
      Name TEXT(40),
      Team TEXT(40),
      Number tinyint,
      Position TEXT(2),
      Age tinyint,
      Height TEXT(4),
      Weight real,
      College TEXT(40),
      Salary float
    )
    WRAPPER csv_fdw
    OPTIONS
      (
         LOCATION = 's3://sqream-demo-data/nba_players.csv',
         RECORD_DELIMITER = '\r\n' -- DOS delimited file
      )
    ;

In the example above the file format is CSV, and it is stored as an S3 object. If the path is on HDFS, you must change the URI accordingly. Note that the record delimiter is a DOS newline (``\r\n``).

For more information, see the following:

* **Creating a foreign table** - see :ref:`create a foreign table<create_foreign_table>`.


Querying Foreign Tables
-----------------------

The following shows the data in the foreign table:

.. code-block:: sql
   
   t=> SELECT * FROM nba LIMIT 10;
   
.. code-block:: none

   name          | team           | number | position | age | height | weight | college           | salary  
   --------------+----------------+--------+----------+-----+--------+--------+-------------------+---------
   Avery Bradley | Boston Celtics |      0 | PG       |  25 | 6-2    |    180 | Texas             |  7730337
   Jae Crowder   | Boston Celtics |     99 | SF       |  25 | 6-6    |    235 | Marquette         |  6796117
   John Holland  | Boston Celtics |     30 | SG       |  27 | 6-5    |    205 | Boston University |         
   R.J. Hunter   | Boston Celtics |     28 | SG       |  22 | 6-5    |    185 | Georgia State     |  1148640
   Jonas Jerebko | Boston Celtics |      8 | PF       |  29 | 6-10   |    231 |                   |  5000000
   Amir Johnson  | Boston Celtics |     90 | PF       |  29 | 6-9    |    240 |                   | 12000000
   Jordan Mickey | Boston Celtics |     55 | PF       |  21 | 6-8    |    235 | LSU               |  1170960
   Kelly Olynyk  | Boston Celtics |     41 | C        |  25 | 7-0    |    238 | Gonzaga           |  2165160
   Terry Rozier  | Boston Celtics |     12 | PG       |  22 | 6-2    |    190 | Louisville        |  1824360
   Marcus Smart  | Boston Celtics |     36 | PG       |  22 | 6-4    |    220 | Oklahoma State    |  3431040
   
Bulk Loading a File from a Public S3 Bucket
-------------------------------------------

The ``COPY FROM`` command can also be used to load data without staging it first.

.. note:: The bucket must be publicly available and objects can be listed.

The following is an example of bulk loading a file from a public S3 bucket:

.. code-block:: postgres

   COPY nba FROM 's3://sqream-demo-data/nba.csv' WITH OFFSET 2 RECORD DELIMITER '\r\n';
   
For more information on the ``COPY FROM`` command, see :ref:`copy_from`.

Loading Files from an Authenticated S3 Bucket
---------------------------------------------

The following is an example of loading fles from an authenticated S3 bucket:

.. code-block:: sql

   COPY nba FROM 's3://secret-bucket/*.csv' WITH OFFSET 2 RECORD DELIMITER '\r\n' 
   AWS_ID '12345678'
   AWS_SECRET 'super_secretive_secret';