12.9. Amazon S3 Translator The Amazon S3 translator, known by the type name amazon-s3 , allows you to use Amazon S3 object resources. The Web Service Data Source resource-adapter is used to access it. Use it together with TEXTTABLE or XMLTABLE table functions to access CSV and XML formatted data. This translator supports access to Amazon S3 using access-key and secret-key. Here is sample VDB that is reading a CSV file from Amazon S3 with name 'g2.txt' in the Amazon S3 bucket called 'teiidbucket': Use the translator override mechanism to supply these properties: Table 12.3. Properties Name Description Default Encoding The encoding that should be used for CLOBs returned by the getTextFiles procedure. The value should match an encoding known to the JRE. The system default encoding. Accesskey Amazon Security Access Key. Log in to Amazon console to find your security access key. When provided, this becomes the default access key n/a Secretkey Amazon Security secret Key. Log in to Amazon console to find your security secret key. When provided, this becomes the default secret key. n/a Region Amazon Region to be used with the request. When provided this will be default region used. n/a Bucket Amazon S3 bucket name, if provided this will serve as default bucket to be used for all the requests n/a Encryption When SSE-C type encryption used, where customer supplies the encryption key, this key will be used for defining the "type" of encryption algorithm used. Supported are AES-256, AWS-KMS. If provided this will be used as default algorithm for all "get" based calls n/a Encryptionkey When SSE-C type encryption used, where customer supplies the encryption key, this key will be used for defining the "encryption key". If provided this will be used as default key for all "get" based calls n/a When you add a model (schema) as in the example, the following procedure calls are available for you to execute against Amazon S3. Note Bucket, region, accesskey, secretkey, encryption and encryptionkey are optional or nullable parameters in most of the methods provided. (You do not need to provide them if they are already configured using translator override properties.) getTextFile(... ) retrieves the given named object as a text file from a specified bucket and region using the provided security credentials as a clob. Note The endpoint is optional. When it is provided, this URL is used instead of the one constructed by the supplied properties. Use encryption and encryptionkey only if server-side security with customer supplied keys (SSE-C) is in force. If stream is set to true, then returned lobs may only be read once and are not buffered to disk. getFile(... ) retrieves the given named object as a binary file from a specified bucket and region using the provided security credentials as a blob. saveFile(... ) save the CLOB, BLOB, or XML value to a given name and bucket. In this procedure, signature contents parameter can be any of the lob types: Note saveFile does NOT support streaming or chunked-based upload of contents. If you try to load very large objects there is a risk of encountering out-of-memory issues. This method does not support SSE-C based security encryption either. deleteFile(... ) deletes the named object from the bucket. Here is an example: list(... ) lists the contents of the bucket. The resulting output is the XML file that Amazon S3 provides: You can parse this into a view: When all properties like bucket, region, accesskey and secretkey are defined as translator override properties one can also simply issue this: Note If there are more then 1000 objects in the bucket, then the value 'NextContinuationToken' need to be supplied as the 'nexttoken' into the list call to fetch the batch of objects. The resource adapter for this translator is provided through the Web Service Data Source .

Commands:
e1,e2,e3 5,'five',5.0 6,'six',6.0 7,'seven',7.0
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="example" version="1"> <model name="s3"> <source name="web-connector" translator-name="user-s3" connection-jndi-name="java:/amazon-s3"/> </model> <model name="Stocks" type="VIRTUAL"> <metadata type="DDL"> CREATE VIEW G2 (e1 integer, e2 string, e3 double,PRIMARY KEY (e1)) AS SELECT SP.e1, SP.e2,SP.e3 FROM (EXEC s3.getTextFile(name=>'g2.txt')) AS f, TEXTTABLE(f.file COLUMNS e1 integer, e2 string, e3 double HEADER) AS SP; </metadata> </model> <translator name="user-s3" type="amazon-s3"> <property name="accesskey" value="xxxx"/> <property name="secretkey" value="xxxx"/> <property name="region" value="us-east-1"/> <property name="bucket" value="teiidbucket"/> </translator> </vdb>
getTextFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey,string encryption, string encryptionkey, boolean stream default false) returns TABLE(file blob, endpoint string, lastModified string, etag string, size long);
exec getTextFile(name=>'myfile.txt'); SELECT SP.e1, SP.e2,SP.e3, f.lastmodified FROM (EXEC getTextFile(name=>'myfile.txt')) AS f, TEXTTABLE(f.file COLUMNS e1 integer, e2 string, e3 double HEADER) AS SP;
getFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey, string encryption, string encryptionkey, boolean stream default false) returns TABLE(file blob, endpoint string, lastModified string, etag string, size long)
exec getFile(name=>'myfile.xml', bucket=>'mybucket', region=>'us-east-1', accesskey=>'xxxx', secretkey=>'xxxx'); select b.* from (exec getFile(name=>'myfile.xml', bucket=>'mybucket', region=>'us-east-1', accesskey=>'xxxx', secretkey=>'xxxx')) as a, XMLTABLE('/contents' PASSING XMLPARSE(CONTENT a.result WELLFORMED) COLUMNS e1 integer, e2 string, e3 double) as b;
call saveFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey, contents object)
exec saveFile(name=>'g4.txt', contents=>'e1,e2,e3\n1,one,1.0\n2,two,2.0');
call deleteFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey)
exec deleteFile(name=>'myfile.txt');
call list(string bucket, string region, string accesskey, string secretkey, nexttoken string) returns Table(result clob)
<?xml version="1.0" encoding="UTF-8"?>/n <ListBucketResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/"> <Name>teiidbucket</Name> <Prefix></Prefix> <KeyCount>1</KeyCount> <MaxKeys>1000</MaxKeys> <IsTruncated>false</IsTruncated> <Contents> <Key>g2.txt</Key> <LastModified>2017-08-08T16:53:19.000Z</LastModified> <ETag>&quot;fa44a7893b1735905bfcce59d9d9ae2e&quot;</ETag> <Size>48</Size> <StorageClass>STANDARD</StorageClass> </Contents> </ListBucketResult>
select b.* from (exec list(bucket=>'mybucket', region=>'us-east-1')) as a, XMLTABLE(XMLNAMESPACES(DEFAULT 'http://s3.amazonaws.com/doc/2006-03-01/'), '/ListBucketResult/Contents' PASSING XMLPARSE(CONTENT a.result WELLFORMED) COLUMNS Key string, LastModified string, ETag string, Size string, StorageClass string, NextContinuationToken string PATH '../NextContinuationToken') as b;
SELECT * FROM Bucket