Ce contenu n'est pas disponible dans la langue sélectionnée.
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':
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>
Use the translator override mechanism to supply these 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.
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);
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.
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(…) retrieves the given named object as a binary file from a specified bucket and region using the provided security credentials as a blob.
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;
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:
call saveFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey, contents object)
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.
exec saveFile(name=>'g4.txt', contents=>'e1,e2,e3\n1,one,1.0\n2,two,2.0');
deleteFile(…) deletes the named object from the bucket.
call deleteFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey)
Here is an example:
exec deleteFile(name=>'myfile.txt');
list(…) lists the contents of the bucket.
call list(string bucket, string region, string accesskey, string secretkey, nexttoken string)
returns Table(result clob)
The resulting output is the XML file that Amazon S3 provides:
<?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>"fa44a7893b1735905bfcce59d9d9ae2e"</ETag>
<Size>48</Size>
<StorageClass>STANDARD</StorageClass>
</Contents>
</ListBucketResult>
You can parse this into a view:
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;
When all properties like bucket, region, accesskey and secretkey are defined as translator override properties one can also simply issue this:
SELECT * FROM Bucket
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 next batch of objects.
The resource adapter for this translator is provided through the
Web Service Data Source.
