HiRDB Connector for Presto 03-00

Copyright (C) 2020 Hitachi, Ltd.

?͂??߂?

HiRDB Connector for Presto???g?p???邱?ƂŁAPresto????HiRDB?ɐڑ????āASQL???????s???邱?Ƃ??ł??܂??B
????Connector???g?p???邱?ƂŁAPresto??ő??̃C???X?^???X?ƌ????Ȃǂ??????Ȃ????Ƃ??ł??܂??B

0.?O??
?{Connector?́Ahttps://prestosql.io/?Ō??J????Ă???presto-server-3XX.tar.gz[3xx??300?ȏ?̐???]??????Connector?ł??B
?ȉ??̊‹??œ???m?F???s???܂????B

Presto
 presto-server-315.tar.gz

JRE
 OpenJDK Runtime Environment (build 1.8.0_212-8u212-b03-0ubuntu1.18.04.1-b03)
 OpenJDK Runtime Environment Corretto-11.0.6.10.1

1.?g?p???@

1.1 catalog properties file?쐬
Presto??catalog?Ƃ???HiRDB Connector for Presto???\?????邽?߂ɁAPresto??etc/catalog??hirdb1.properties?Ȃǂ̖??O??catalog properties file???쐬???AHiRDB Connector for Presto??ݒ肵?Ă????????B
catalog properties file?ɂ́A???̍??ڂ?ݒ肵?Ă????????B
?e???ڂ́A?‹??ɍ??킹???l?ɒu???????Ă????????B

---- catalog?t?@?C???̐ݒ???e???????? ----
connector.name=hirdb
connection-url=jdbc:hitachi:hirdb[??1]
connection-user=[?F?Ž??ʎq][??2]
connection-password=[?p?X???[?h][??2]
case-insensitive-name-matching=true[??3]
case-insensitive-name-matching.cache-ttl=1m[??3]
---- catalog?t?@?C???̐ݒ???e?????܂? ----

??1?F
?uUAP?J???K?C?h?v?́ugetConnection???\?b?h?ɂ??HiRDB?ւ̐ڑ??v?́uURL?\???v??ݒ肵?Ă???????
 Presto?̓????ł́A??????utf-8?Ƃ??ď??????s???̂ŁAHiRDB?̕????R?[?h??utf-8?ȊO?̏ꍇ?A?K?v?ɉ?????ENCODELANG??ݒ肵?Ă????????B

??2?F
?F?Ž??ʎq?A?p?X???[?h?ɉp?????????܂ޏꍇ?A???p??(")?ň͂??Ă????????B

??3?F
Presto?́ASQL???̉?͎??ɁASQL?????̎??ʎq??S?ď??????ɕϊ????Ă??܂??B
HiRDB?Ȃǂ̎??ʎq?ɑ啶???Ə??????????݂???DBMS?̏ꍇ?Acase-insensitive-name-matching=true??ݒ肵?ď??????̎??ʎq?ƁA???݂??鎯?ʎq???}?b?`???O????K?v??????܂??B
???̂???HiRDB Connector for Presto???Ŗ???????true?ɂ??Ă??܂??B
?}?b?`???O???ʂ́APresto???ŃL???b?V?????A?}?b?`???O???????????????Ă??܂??B
???̃L???b?V?????????????鎞?Ԃ?case-insensitive-name-matching.cache-ttl??io.airlift.units.Duration.valueOf???\?b?h?̈????̌`???Őݒ肵?Ă????????B
?p?ɂɒ?`???̕ύX???s???‹??ł́Acase-insensitive-name-matching.cache-ttl?̒l??Z???ݒ肵?Ă????????B
Presto?g?p???ɕ\??`??ύX???Ȃ??ꍇ?́Acase-insensitive-name-matching.cache-ttl?̒l?𒷂????Ă????????B
??L??1m??1?????Ӗ????܂??B
case-insensitive-name-matching.cache-ttl?̎w?肪?Ȃ??ꍇ?́A1???????肵?܂??B

1.2 plugin?t?H???_?쐬
plugin?t?H???_?Ɂuhirdb?v?t?H???_???쐬???A???̃t?H???_???Ɉȉ??̃t?@?C?????i?[???Ă????????B
---- plugin/hirdb?t?H???_?Ɋi?[????t?@?C?????????? ----
?Epresto-hirdb-03-00.jar(?{???C?u????)
?Epdjdbc4.jar(HiRDB ?T?[?o???̓N???C?A???g????R?s?[???Ă???????)
?@?ȉ???Presto?̃o?C?i??????R?s?[???Ă????????BXXX??Presto?o?C?i???Ɋ܂܂??Ă???o?[?W?????ɂ??Ă????????B
?Epresto-base-jdbc-XXX.jar (presto-hirdb-03-00?̃X?[?p?[?N???X)
?Ebootstrap-XXX.jar (????ȍ~?̓X?[?p?[?N???X???K?v?Ƃ???N???X)
?Ebval-jsr-XXX.jar
?Econfiguration-XXX.jar
?Eguava-XXX-jre.jar
?Eguice-XXX.jar
?Ejavax.annotation-api-X.X.X.jar
?Ejavax.inject-XXX.jar
?Ejoda-time-XXX.jar
?Elog-XXX.jar
?Evalidation-api-XXX.Final.jar
---- plugin/hirdb?t?H???_?Ɋi?[????t?@?C???????܂? ----

???g?p????JRE?APresto?̃o?[?W?????ɂ????presto-base-jdbc-XXX.jar???K?v?Ƃ???p?b?P?[?W???ς??”\????????܂??B
???̂Ƃ??́A????plugin?̃t?H???_????K?X?R?s?[???s???Ă????????B

2.????HiRDB?ڑ?
1?‚?HiRDB Connector for Presto?́A1?‚?HiRDB?̃f?[?^?x?[?X?ɐڑ??ł??܂??B
??????HiRDB?̃f?[?^?x?[?X?ɐڑ????????ꍇ?AHiRDB Connector for Presto?̃C???X?^???X?𕡐??ݒ肵?Ă????????B
?Ⴄ?J?^???O??lj?????ɂ́Aetc/catalog?Ɋg???q??.properties?Ƃ??ĈقȂ閼?O?̃t?@?C?????쐬???Ă????????B
?Ⴆ?΁Asales.properties?Ƃ??????̂̃v???p?e?B?t?@?C?????쐬?????ꍇ?APresto?̓v???p?e?B?t?@?C?????Ŏw?肳?ꂽconnecter???g????sales?Ƃ??????̂̃J?^???O???쐬???܂??B

3.Querying HiRDB
HiRDB Connector for Presto?́Aconnection-user?Ɏw?肵???F?Ž??ʎq?̃X?L?[?}?ɐڑ??????܂??B
SHOW SCHEMAS?????s???邱?ƂŁAHiRDB?ŗ??p?”\?ȃX?L?[?}???????܂??B

SHOW SCHEMAS FROM hirdb1;

?X?L?[?}?uweb?v?Ƃ??????̂????݂???ꍇ?A???̃X?L?[?}???ɑ??݂???S?Ă̕\??SHOW TABLES?Ō??邱?Ƃ??ł??܂??B

SHOW TABLES FROM hirdb1.web;

?X?L?[?}?uweb?v?̕\?uclicks?v?̗?̃??X?g?????̂????ꂩ?̍\???Ō??邱?Ƃ??ł??܂??B

DESCRIBE hirdb1.web.clicks;
SHOW COLUMNS FROM hirdb1.web.clicks;

?X?L?[?}?uweb?v?̕\?uclicks?v??????????ꍇ?́A???̂Ƃ???ł??B

SELECT * FROM hirdb1.web.clicks;

4?D????????

4.1???O?̎w??̐???
Presto?̎d?l??A???Ƃ????p??(")?Ŗ??O?̎w??(?\???A?񖼂Ȃ?)???͂??Ă??APresto?͑啶???Ə?????????ʂ??܂???B[R1]
?]???āA?啶???Ə??????𖳎????????????r?ŁA????̖??̂ƂȂ?\??????????ꍇ?́A???̕\?ɑ΂???SQL???????s???邱?Ƃ??ł??܂???B[R2]
SQL?????Ɏw?肵???\?ŁA?啶???Ə??????𖳎????????????r?ŁA????̖??̂ƂȂ?񂪕??????݂???ꍇ?A???Ƃ?SQL?????ɂ??̗񂪏o?????Ȃ??Ă??A????SQL???????s???邱?Ƃ??ł??܂???B[R3]

??F
catalog hirdb1?ɕ\"user1"."T1"(C1,C2,C3)?ƕ\"user1"."t1"(C1,C2,C3)?ƕ\"user1"."T2"(C1,C2,C3)?ƕ\"user1"."T3"("c1",C1,C2)??4?‚̕\?????݂???ꍇ
?@select * from hirdb1.user1."t2"[R1?ɂ??T2??????????]
?@select * from hirdb1.user1.t1[R2?ɂ??A?G???[]
?@select "c2" from hirdb1.user1.t3[R3?ɂ??A?G???[]

[R2][R3]?̂悤?ȏꍇ?APresto????A?N?Z?X???邽?߂̕ʖ???VIEW?\???`???ăA?N?Z?X???Ă????????B
??Ƃ??āA"user".T3?̏ꍇ?͈ȉ??̂悤??VIEW?\???쐬???āA????VIEW?\?o?R?ŃA?N?Z?X???Ă????????B
CREATE VIEW "v1"(C1,C2) AS SELECT ("c1",C1) FROM "user".T3

4.2?????̂???Presto?̍\??
?ȉ???Presto?̍\???́A?Ή????Ă??܂???B(?쐬????Presto-315?ł͊??N???X???Ή????Ă??Ȃ?????)
?EALTER SCHEMA
?EANALYZE
?ECALL
?ECOMMENT
?ECOMMIT ??4
?ECREATE SCHEMA
?ECREATE VIEW
?EDELETE
?EDROP SCHEMA
?EDROP TABLE
?EDROP VIEW
?EGRANT
?EREVOKE
?EROLLBACK ??4
?ESHOW CREATE VIEW
?ESHOW GRANTS
?ESHOW ROLES
?ESHOW ROLE GRANTS
?ESTART TRANSACTION ??4
?EUPDATE

??4
?g?????U?N?V?????́APresto???Ŗ??????ɃI?[?g?R?~?b?g????܂??B
???̂??߁A???[?U???ł̖????I?ȃg?????U?N?V?????̌????͂ł??܂???B

4.3?????̂???HiRDB?̃f?[?^?^
?ȉ??̃f?[?^?^?͑Ή????Ă??܂???
?ETIMESTAMP(2),TIMESTAMP(4),TIMESTAMP(6)(?~???b???x?ɂ??Ă???ꍇ?͉”\?B2020-01-01 12:34:56.789000)
?EBLOB
?EINTERVAL YEAR TO DAY
?EINTERVAL HOUR TO SECOND
?E?J?Ԃ???

4.4???̑?????
LANG??utf-8??ݒ肵?Ă??Ȃ??V?F????presto-cli?????s????ƁA???{?ꂪ???????????邱?Ƃ?????܂??B
?\???ʎq?A?񖼂ɑS?p???????܂܂??ꍇ?Adictionary datatype mchar=use???Ȃ??Ɛ??????\???ʎq?A?񖼂??擾?ł??Ȃ????Ƃ?????܂??B
dictionary datatype mchar=nouse?̏ꍇ?A???O?̎w??ɑS?p???????܂܂Ȃ?VIEW?\??ʓr?p?ӂ??ăA?N?Z?X???Ă????????B

--------------------------------------------------------------------------------
HITACHI?AHiRDB?́A??????? ???????쏊?̏??W?܂??͓o?^???W?ł??B
???̑??L?ڂ̉?Ж??A???i???Ȃǂ́A???ꂼ??̉?Ђ̏??W???????͓o?^???W?ł??B