Sure I'll do that :) Best Regards, Hari
On Tue, 25 Sep 2018, 09:05 ShaoFeng Shi, <[email protected]> wrote: > Hi Hari, > > Thanks for your comment. The default schema is supported, but please aware > that the 'default' is a keyword in Calcite, so in the SQL please use > "DEFAULT" to escape. For example: > > select count(*) from "DEFAULT".kylin_sales > > Regarding the catalog issue, would you like to report a JIRA to track the > problem? Kylin should follow the JDBC standard as much as possible. > > hosur narahari <[email protected]> 于2018年9月24日周一 下午8:14写道： > >> Hi, >> >> Yes whatever you said about mysql is true. But when queried from JDBC >> driver, the values in column "SCHEMA_NAME" is returned for *getCatalogs() >> *call and *getSchemas()* return empty resultset. >> >> All databases' jdbc drivers have one thing in common. If catalog or >> schema is not empty, it can be used in select query to get the data. >> >> I'll explain the scenario where I'm facing problem currently. I'm trying >> to integrate Kylin with query engines like presto and drill, since Kylin is >> one of the very few open source cube engines. All query engines query as >> [catalogName.][schemaName.]tableName. So both will fail to query from >> Kylin. By adhering it to JDBC standards, we can make integration of Kylin >> with other open source tools easier. >> >> Also is it possible to actually create Catalog in Kylin. If so I can test >> creating custom catalog and see how it works. >> >> Best Regards, >> Hari >> >> On Mon, Sep 24, 2018 at 5:10 PM Xiaoxiang Yu <[email protected]> >> wrote: >> >>> Hi, >>> >>> I interested in your question and I check it carefully. I want to share >>> what I found and I guess it will be a bit helpful. >>> >>> >>> >>> It is a problem about how to realize/obey SQL standard and JDBC >>> standard. You think JDBC standard/SQL standard should support [ [ >>> catalogName . ] schemaName . ] tableName in *from clause*. I think it >>> is right, I found a link(https://calcite.apache.org/docs/reference.html), >>> apache calcite says they can resolve that pattern. >>> >>> >>> >>> *tablePrimary:* >>> >>> * [ [ catalogName . ] schemaName . ] tableName* >>> >>> * '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'* >>> >>> * | tablePrimary [ EXTEND ] '(' columnDecl [, columnDecl ]* ')'* >>> >>> * | [ LATERAL ] '(' query ')'* >>> >>> * | UNNEST '(' expression ')' [ WITH ORDINALITY ]* >>> >>> * | [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, >>> expression ]* ')' ')'* >>> >>> >>> >>> >>> >>> And in JDBC interface these two methods as defined as followed: >>> (java.sql.DatabaseMetaData) >>> >>> [image: * * * * * * * Retrieves the schema names available in this >>> database. are ordered by and <P>The schema columns are: COL > >>> <LI><B>TABLE_SCHEM</B> String {Ocode =>} schema name The results String >>> {ä)code catalog name (may be <code>null</code>) aparam catalog a catalog >>> name; must match the catalog name as it is stored in the database; >>> retrieves those without a catalog; null means catalog name should not be >>> used to narrow down the search. aparam schemapattern a schema name; must >>> match the schema name as it is stored in the database; null means schema >>> name should not be used to narrow down the search. areturn a object in >>> which each row is a schema description Dexception SQLException if a >>> database access error occurs asee #getSearchStringEscape asince 1.6 >>> ResultSet —(string catalog, String schemapattern) throws SQLException;] >>> >>> >>> >>> [image: * Retrieves the catalog names available in this database. * are >>> ordered by catalog name. * catalog column is: * String {acode =>} catalog >>> name The results * areturn a object in which each row has a single >>> <code>String</code> column that is a catalog name * aexception SQLException >>> if a database access error occurs ( ) throws SQLException; ResultSet] >>> >>> >>> >>> And I write a simple program to test behavior of MySQL JDBC driver and Kylin >>> JDBC driver. Here are the code and output. >>> >>> MySQL did not return empty when call *getSchemas* and return four items >>> in *getCatalogs* >>> >>> >>> >>> [image: public static void main(string[] args) throws Exception { >>> testDriver( •tariverNameSD "org.apache.kylin.jdbc.Driver" , " 7079/1acus" , >>> cuserrqame:' "ADMIN", coassworoo "IQ'LIN"); testDriver( 'griverNametSD >>> "com.mysql . jdbc . Driver" , " jdbc :mysql ://localhost: 3306" , " root" , >>> "lacus" ) ; public static void testDriver(String driverName, String url, >>> String userName, + driverName); Class . forName(driverName) ; Connection >>> conn = DriverManager.getConnection(ur1, userName, password); getMetaData( ) >>> ; DatabaseMetaData db conn. ResultSet catalogSet = db. getcatalogs() String >>> catalog = " while (catalogset.next()) { String password) throws Exception { >>> .getschemas(), Resultset schemaset = db String schema " "; while >>> (schemaset.next()) { " + "TABLE_CATALOG")); close() conn .] >>> >>> >>> >>> [image: /Library/Java/JavaVirtuaIMachines/jdk1.8.Ø_181. >>> jdk/Contents/Home/bin/java . Test log4j • log4j • log4j • TABLE TABLE >>> org.apache.kylin.jdbc.Driver •WARN No appenders could be found for logger >>> (org.apache.kylin.jdbc.KylinConnection) . •WARQ Please initialize the log4j >>> system properly. •WARN See >>> http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. _CAT >>> defaultCatalog TABLE_SCHEM LACUS _CATALOG defaultCatalog Test >>> com.mysql.jdbc.Driver Loading class 'com.mysql.jdbc.Driver'. This is >>> deprecated. The new driver class is •com.mysql.cj.jdbc.Driver'. The driver >>> is automatically regis driver class is generally unnecessary. Mon Sep 24 >>> 17:46:33 CST 2018 WAR•J: Establishing SSL connection without server's >>> identity verification is not recommended. According to MySQL 5.5 connection >>> must be established by default if explicit option isn't set. For compliance >>> with existing applications not using SSL the verifyServf You TABLE TABLE >>> TABLE TABLE need either to explicitly disable SSL by setting useSSL=false, >>> or set useSSL=true and provide truststore for server certificate >>> verificatit _CAT _CAT _CAT _CAT information_schema mysql performance_schema >>> sys] >>> >>> >>> >>> >>> >>> Here is code of how MySQL JDBC do.(com.mysql.jdbc.DatabaseMetadata) >>> >>> [image: ( ) throws SQLException { public ResultSet ResultSet results >>> null; Statement stmt = nut Z; try { - this. conn.createstatement(); stmt - >>> stmt . setEscapeprocessing( false); stmt. executeQuery( "SHOW DATABASES"); >>> results = ResultsetMetaData resultsMD = results.getMetaData( fields new >>> Field( " = new ArrayList(); ArrayList tuples eolumnNamee "TABLE _ CAT" , >>> (LßDcTypeVD 12 , resul tsMD. getCoIumnD - new acolum rowval tuples. add(new >>> ByteArrayRow(rowVa1, this. getExceptionIn tuples); Resultset var18 = this. >>> return var18; finally { if (results null) { try { results . } catch >>> (SQLException var16) { Assertion Fai led Except ion . shouldNotHappen( >>> var16) ; eptor())); results = null; if (stmt != null) { try { stmt } catch >>> (SQLException var15) { Assert ion Failed Exception . shouldNotHappen( >>> var15) ; - null; stmt -] >>> >>> >>> >>> [image: public ResultSet Field[] fields hemas() throws SQLException { = >>> new Field( catablet•ämeo " " , "TABLE_SCHEM", CLBDCTYP%DI, .:tengtmJJ O ) , >>> new Field( , ArrayList tuples = new ArrayList(); ResultSet results - >>> tuples); - this. return results;] >>> >>> >>> >>> >>> >>> Let us look how mysql store infomation of catalog/schema/table, It seems >>> that MySQL JDBC did not use column CATALOG_NAME of table SCHEMATA. >>> >>> >>> >>> >>> >>> *My opinion:* >>> >>> So, MySQL JDBC did not follow standard strictly . As far as I concerned, >>> kylin jdbc driver may not need to follow SQL standard so strictly. >>> >>> You can avoid misleading/error message if you do not use [ [ >>> catalogName . ] schemaName . ] tableName pattern in your *from clause*. >>> >>> >>> >>> >>> But I think Kylin should not return a ugly error stacktrace when user >>> use that pattern. I think I may find a better way to avoid such ugly error >>> stacktrace. >>> >>> >>> >>> >>> >>> Link: >>> >>> >>> https://stackoverflow.com/questions/7022755/whats-the-difference-between-a-catalog-and-a-schema-in-a-relational-database >>> >>> https://en.wikipedia.org/wiki/Database_catalog >>> >>> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt >>> >>> >>> >>> >>> >>> If I mistake anything, please let me know. >>> >>> Best Regards, >>> >>> Xiaoxiang Yu >>> >>> *发件人**: *hosur narahari <[email protected]> >>> *答复**: *"[email protected]" <[email protected]> >>> *日期**: *2018年9月20日 星期四 11:43 >>> *收件人**: *"[email protected]" <[email protected]> >>> *主题**: *select with catalog fails >>> >>> >>> >>> Hi, >>> >>> >>> >>> I was using kylin JDBC driver to fetch data. By using DatabaseMetaData >>> if we get catalog using getCatalogs() method, it return value >>> "defaultCatalog". It returns actual hive schema when we execute >>> getSchemas(). >>> >>> >>> >>> According to JDBC contract, catalog.schema.table should be valid from >>> clause and many query layers use that. But kylin fails when we execute that >>> query. >>> >>> I've tried to write sample code piece for that below. >>> >>> >>> >>> *DatabaseMetaData db = conn.getMetaData();* >>> >>> * ResultSet catalogSet = db.getCatalogs();* >>> >>> * String catalog = "";* >>> >>> * if(catalogSet.next()) {* >>> >>> * catalog = catalogSet.getString("TABLE_CAT");* >>> >>> * }* >>> >>> * ResultSet schemaSet = db.getSchemas();* >>> >>> * String schema = "";* >>> >>> * if(schemaSet.next()) {* >>> >>> * schema = schemaSet.getString("TABLE_SCHEM");* >>> >>> * }* >>> >>> * StringBuilder sb = new StringBuilder("SELECT * FROM ");* >>> >>> * if(!catalog.isEmpty()) {* >>> >>> * sb.append(catalog + ".");* >>> >>> * }* >>> >>> * if(!schema.isEmpty()) {* >>> >>> * sb.append(schema + ".");* >>> >>> * }* >>> >>> * sb.append("kylin_sales limit 10");* >>> >>> * String query = sb.toString();* >>> >>> * Statement stat = conn.createStatement();* >>> >>> * ResultSet rs = stat.executeQuery(query);* >>> >>> * while(rs.next()) {* >>> >>> * System.out.println(rs.getObject("trans_id"));* >>> >>> * }* >>> >>> >>> >>> In short, the above snippet is executing the query, >>> >>> *select * from defaultCatalog.DEFAULT.kylin_sales.* >>> >>> >>> >>> Same thing happens even with different schemas if we have like, >>> >>> *select * from defaultCatalog.test.kylin_sales* also fails. >>> >>> >>> >>> Also if hive schema is anything other than default, then <schema >>> name>.<table name> in from clause works. But with default schema it fails. >>> >>> >>> >>> Ex. If tables are in *test *schema of hive, then *select * from >>> test.kylin_sales *works. But when the tables are in *default *schema of >>> hive, then *select * from default.kylin_sales *fails. >>> >>> >>> >>> Please let me know if I'm doing something wrong. >>> >>> >>> >>> Best Regards, >>> >>> Hari >>> >> > > -- > Best regards, > > Shaofeng Shi 史少锋 > >
