{Denodo}データソース・ラッパー・基本ビュー・インターフェイスビューの作成(S3)

前提:
Denodo Express インストール、起動済み

-- 1. コマンド等のインストール

-- 1.1 aws cli version 2 インストール

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install
aws --version

-- 1.2 jqインストール
sudo yum -y install jq

-- 2. S3 バケットを作成する

aws s3 ls

aws s3 mb s3://bucket123

-- 3. テストデータ作成

echo "1,10,AAA" > f001.txt
echo "2,20,BBB" >> f001.txt

aws s3 cp f001.txt s3://bucket123/tab1/f001.txt

aws s3 ls s3://bucket123 --recursive

-- 4. 署名バージョン 4 の動作確認

export BUCKET=bucket123
export FILE=tab1/f001.txt
export TIMESTAMP=20230422T063500Z
export AWS_ACCESS_KEY_ID=AAAAAAAAAAAAAAAAAAAA
export AWS_SECRET_ACCESS_KEY=BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
export AWS_REGION=ap-northeast-1

echo $BUCKET
echo $FILE
echo $TIMESTAMP
echo $AWS_ACCESS_KEY_ID
echo $AWS_SECRET_ACCESS_KEY
echo $AWS_REGION

YYYYMMDD=${TIMESTAMP:0:8}
echo $YYYYMMDD

-- 4.1 署名バージョン 4 の正規リクエストを作成しハッシュ化する

HCR=$(echo -en "GET\\n/${FILE}\\n\\nhost:${BUCKET}.s3-${AWS_REGION}.amazonaws.com\\nx-amz-content-sha256:e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855\\nx-amz-date:${TIMESTAMP}\\n\\nhost;x-amz-content-sha256;x-amz-date\\ne3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855" | \
openssl dgst -sha256 | \
awk '{print $2}')

echo $HCR

-- 4.2 署名バージョン 4 の署名文字列と署名キーを作成し署名を計算する

vi signing_key.sh

#!/bin/bash
function hmac_sha256 {
key="$1"
data="$2"
echo -n "$data" | openssl dgst -sha256 -mac HMAC -macopt "$key" | sed 's/^.* //'
}

secret="$1"
date="$2"
region="$3"
service="$4"

# Four-step signing key calculation
dateKey=$(hmac_sha256 key:"AWS4$secret" $date)
dateRegionKey=$(hmac_sha256 hexkey:$dateKey $region)
dateRegionServiceKey=$(hmac_sha256 hexkey:$dateRegionKey $service)
signingKey=$(hmac_sha256 hexkey:$dateRegionServiceKey "aws4_request")

echo $signingKey

chmod +x signing_key.sh

SKEY=$(./signing_key.sh ${AWS_SECRET_ACCESS_KEY} ${YYYYMMDD} ${AWS_REGION} s3)

echo $SKEY

SIGN=$(echo -en "AWS4-HMAC-SHA256\\n${TIMESTAMP}\\n${YYYYMMDD}/${AWS_REGION}/s3/aws4_request\\n${HCR}" | \
openssl dgst -sha256 -mac HMAC -macopt hexkey:${SKEY} | \
awk '{print $2}')

echo $SIGN

-- 4.3 HTTP リクエストに署名を追加する

curl -v https://${BUCKET}.s3-${AWS_REGION}.amazonaws.com/${FILE} \
-H "Authorization: AWS4-HMAC-SHA256 Credential=${AWS_ACCESS_KEY_ID}/${YYYYMMDD}/${AWS_REGION}/s3/aws4_request,SignedHeaders=host;x-amz-content-sha256;x-amz-date,Signature=${SIGN}" \
-H "x-amz-content-sha256: e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855" \
-H "x-amz-date: ${TIMESTAMP}"

-- 5. データベースとフォルダの作成

-- 5.1 データベース作成
DROP DATABASE db06 cascade;

CREATE OR REPLACE DATABASE db06 'description of db06'
VCS OFF
CHARSET UNICODE
AUTHENTICATION LOCAL
ODBC AUTHENTICATION NORMAL
CHECK_VIEW_RESTRICTIONS ALWAYS
;

-- 5.2 フォルダ作成
CONNECT DATABASE db06;

CREATE FOLDER '/01ds' DESCRIPTION 'description of folder01';
CREATE FOLDER '/02bv' DESCRIPTION 'description of folder02';
CREATE FOLDER '/03iv' DESCRIPTION 'description of folder03';

-- 6. データソース作成

CREATE OR REPLACE DATASOURCE DF ds01
FOLDER = '/01ds'
IGNOREMATCHINGERRORS = FALSE
ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'https://@{param_host}@{param_filepath}'
HEADERS (
'Authorization'='@{param_auth}',
'x-amz-content-sha256'='e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855',
'x-amz-date'='@{param_date}'
)
AUTHENTICATION OFF
PROXY OFF
COLUMNDELIMITER = ','
ENDOFLINEDELIMITER = '\n'
HEADER = FALSE;

-- 7. ラッパー作成

CREATE OR REPLACE WRAPPER DF ds01
FOLDER = '/02bv'
DATASOURCENAME=ds01
OUTPUTSCHEMA (
param_date = 'param_date' (OBL) (DEFAULTVALUE='20230422T063500Z') EXTERN,
param_auth = 'param_auth' (OBL) (DEFAULTVALUE='AWS4-HMAC-SHA256 Credential=AAAAAAAAAAAAAAAAAAAA/20230422/ap-northeast-1/s3/aws4_request,SignedHeaders=host;x-amz-content-sha256;x-amz-date,Signature=CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC') EXTERN,
"PARAM_HOST" = 'PARAM_HOST' (OBL) (DEFAULTVALUE='bucket123.s3-ap-northeast-1.amazonaws.com') EXTERN,
"PARAM_FILEPATH" = 'PARAM_FILEPATH' (OBL) (DEFAULTVALUE='/tab1/f001.txt') EXTERN,
"1" = '1',
"10" = '10',
"AAA" = 'AAA'
);

-- 8. 基本ビュー作成

CREATE OR REPLACE TABLE b_tab1 I18N jp (
param_date:text (extern),
param_auth:text (extern),
"PARAM_HOST":text (extern),
"PARAM_FILEPATH":text (extern),
"1":text,
"10":text,
"AAA":text
)
FOLDER = '/02bv'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD ds01(
I18N jp
CONSTRAINTS (
ADD param_date (=) OBL ONE
ADD param_auth (=) OBL ONE
ADD "PARAM_HOST" (=) OBL ONE
ADD "PARAM_FILEPATH" (=) OBL ONE
ADD "1" NOS ZERO ()
ADD "10" NOS ZERO ()
ADD "AAA" NOS ZERO ()
)
OUTPUTLIST ("1", "10", "AAA"
)
WRAPPER (df ds01)
);

-- 9. インターフェイスビュー作成

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
param_date:text,
param_auth:text,
"PARAM_HOST":text,
"PARAM_FILEPATH":text,
"1":text,
"10":text,
"AAA":text
)
SET IMPLEMENTATION b_tab1
FOLDER = '/03iv';

-- 10. 動作確認

echo "AWS4-HMAC-SHA256 Credential=${AWS_ACCESS_KEY_ID}/${YYYYMMDD}/${AWS_REGION}/s3/aws4_request,SignedHeaders=host;x-amz-content-sha256;x-amz-date,Signature=${SIGN}"

SELECT * FROM db06.b_tab1
WHERE param_auth = 'AWS4-HMAC-SHA256 Credential=AAAAAAAAAAAAAAAAAAAA/20230422/ap-northeast-1/s3/aws4_request,SignedHeaders=host;x-amz-content-sha256;x-amz-date,Signature=CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
AND param_date = '20230422T063500Z'
AND "PARAM_FILEPATH" = '/tab1/f001.txt'
AND "PARAM_HOST" = 'bucket123.s3-ap-northeast-1.amazonaws.com'
CONTEXT('cache_wait_for_load' = 'true')
;

SELECT * FROM db06.i_tab1
WHERE param_auth = 'AWS4-HMAC-SHA256 Credential=AAAAAAAAAAAAAAAAAAAA/20230422/ap-northeast-1/s3/aws4_request,SignedHeaders=host;x-amz-content-sha256;x-amz-date,Signature=CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
AND param_date = '20230422T063500Z'
AND "PARAM_FILEPATH" = '/tab1/f001.txt'
AND "PARAM_HOST" = 'bucket123.s3-ap-northeast-1.amazonaws.com'
CONTEXT('cache_wait_for_load' = 'true')
;

-- 11. クリーンアップ

-- バケットの削除

aws s3 ls

aws s3 rb s3://bucket123 --force