前提:
Denodo Express インストール、起動済み
-- 1. 前作業
gcloud init
gcloud auth list
gcloud --version
gcloud projects create project01-9999999 \
--name="project01"
gcloud config list
gcloud config set project project01-9999999
gcloud config set compute/region asia-northeast1 --quiet
gcloud config set compute/zone asia-northeast1-a --quiet
gcloud beta billing accounts list
gcloud beta billing projects link project01-9999999 --billing-account=111111-111111-111111
gcloud services enable compute.googleapis.com --project project01-9999999
gcloud components update
-- 2. Create an oAuth Client ID credentials
Credentials
-- 2.1 OAuth 同意画面設定
公開ステータス: テスト
テストユーザーに自分のアカウントを追加
-- 2.2 OAuth 2.0 クライアント ID の作成
デスクトップアプリ
JSONをダウンロード
-- 3. バケットの作成
gcloud storage buckets create gs://bucket123 \
--default-storage-class=Standard \
--no-enable-autoclass \
--location=asia-northeast1 \
--public-access-prevention \
--uniform-bucket-level-access
gcloud storage ls
-- 4. ファイルのアップロード
echo "col1,col2,col3" > f001.txt
echo "1,10,AAA" >> f001.txt
echo "2,20,BBB" >> f001.txt
gcloud storage cp f001.txt gs://bucket123
gcloud storage ls --long gs://bucket123/*
-- 5. データベースとフォルダの作成
-- 5.1 データベース作成
CONNECT DATABASE admin;
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'
ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'https://storage.googleapis.com/storage/v1/b/bucket123/o/f001.txt?alt=media'
AUTHENTICATION OAUTH20 (
ACCESSTOKEN = 'XXXXX' ENCRYPTED
REQUESTSIGNINGMETHOD = HEADER
AUTHENTICATION_GRANT = CODE_GRANT
TOKENENDPOINTURL = 'https://oauth2.googleapis.com/token'
CLIENTIDENTIFIER = '111111111111-AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.apps.googleusercontent.com'
CLIENTSECRET = 'XXXXX' ENCRYPTED
REFRESHTOKEN = 'XXXXX' ENCRYPTED
ACCESSTOKENEXPIRESIN = 3599
AUTHORIZATIONSERVERURL = 'https://accounts.google.com/o/oauth2/auth'
REDIRECTIONENDPOINTURL = DEFAULT
SCOPES = 'https://www.googleapis.com/auth/devstorage.read_only'
SETSTATEPARAMETER = true
)
PROXY OFF
COLUMNDELIMITER = ','
ENDOFLINEDELIMITER = '\n'
HEADER = TRUE;
-- 7. ラッパー作成
CREATE OR REPLACE WRAPPER DF ds01
FOLDER = '/02bv'
DATASOURCENAME=ds01
OUTPUTSCHEMA (
col1 = 'col1',
col2 = 'col2',
col3 = 'col3'
);
-- 8. 基本ビュー作成
CREATE OR REPLACE TABLE b_tab1 I18N jp (
col1:text,
col2:text,
col3:text
)
FOLDER = '/02bv'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD ds01(
I18N jp
CONSTRAINTS (
ADD col1 NOS ZERO ()
ADD col2 NOS ZERO ()
ADD col3 NOS ZERO ()
)
OUTPUTLIST (col1, col2, col3
)
WRAPPER (df ds01)
);
-- 9. インターフェイスビュー作成
CREATE OR REPLACE INTERFACE VIEW i_tab1 (
col1:text,
col2:text,
col3:text
)
SET IMPLEMENTATION b_tab1
FOLDER = '/03iv';
-- 10. 動作確認
SELECT * FROM db06.b_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE
SELECT * FROM db06.i_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE
-- 11. クリーンアップ
gcloud storage rm gs://bucket123 --recursive
gcloud storage ls
gcloud projects list
gcloud projects delete project01-9999999 \
--quiet