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 史少锋
