主页
产品
Red Hat JBoss Data Virtualization
6.4
Development Guide Volume 3: Reference Material
12.9. Amazon S3 Translator

此内容没有您所选择的语言版本。

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

e1,e2,e3
5,'five',5.0
6,'six',6.0
7,'seven',7.0

Copy to Clipboard

Toggle word wrap

<?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>

<?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>

Copy to Clipboard

Toggle word wrap

Use the translator override mechanism to supply these properties:

Expand

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.

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);

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);

Copy to Clipboard

Toggle word wrap

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;

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;

Copy to Clipboard

Toggle word wrap

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)

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)

Copy to Clipboard

Toggle word wrap

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;

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;

Copy to Clipboard

Toggle word wrap

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)

call saveFile(string name NOT NULL, string bucket, string region, string endpoint,
   string accesskey, string secretkey, contents object)

Copy to Clipboard

Toggle word wrap

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');

exec saveFile(name=>'g4.txt', contents=>'e1,e2,e3\n1,one,1.0\n2,two,2.0');

Copy to Clipboard

Toggle word wrap

deleteFile(…) deletes the named object from the bucket.

call deleteFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey)

call deleteFile(string name NOT NULL, string bucket, string region, string endpoint, string accesskey, string secretkey)

Copy to Clipboard

Toggle word wrap

Here is an example:

exec deleteFile(name=>'myfile.txt');

exec deleteFile(name=>'myfile.txt');

Copy to Clipboard

Toggle word wrap

list(…) lists the contents of the bucket.

call list(string bucket, string region, string accesskey, string secretkey, nexttoken string)
    returns Table(result clob)

call list(string bucket, string region, string accesskey, string secretkey, nexttoken string)
    returns Table(result clob)

Copy to Clipboard

Toggle word wrap

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>&quot;fa44a7893b1735905bfcce59d9d9ae2e&quot;</ETag>
        <Size>48</Size>
        <StorageClass>STANDARD</StorageClass>
    </Contents>
</ListBucketResult>

<?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>

Copy to Clipboard

Toggle word wrap

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;

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;

Copy to Clipboard

Toggle word wrap

When all properties like bucket, region, accesskey and secretkey are defined as translator override properties one can also simply issue this:

SELECT * FROM Bucket

SELECT * FROM Bucket

Copy to Clipboard

Toggle word wrap

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.

返回顶部

此内容没有您所选择的语言版本。

12.9. Amazon S3 Translator

学习

尝试、购买和销售

社区

关于红帽文档

让开源更具包容性

關於紅帽

Theme

Red Hat legal and privacy links

Red Hat legal and privacy links