lens-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajat Khandelwal <rajatgupt...@gmail.com>
Subject Re: Trying to add Derby DB as a Lens data source.
Date Wed, 14 Sep 2016 12:07:01 GMT
>From the error it looks like the db "db1" is not present in your schema.
Can you double check by connecting by another client e.g. squirrel?

Another thing to try is to use jdbc:derby:/apache-
lens-2.5.0-beta-bin/server/mydb1;create=true

On Wed, Sep 14, 2016 at 4:35 PM Pawan Malwal <pawan.malwal@huawei.com>
wrote:

> Hi Puneet,
>
>
>
> Thanks for your reply.
>
> There was some derby version conflict. I was using db-derby-10.12.1.1  but
> I guess Lens supports db-derby-10.10.1.1 as I see derby-10.10.1.1.jar in
> ~/apache-hive-0.13.4-inm-bin/lib/ directory. So after installing
> db-derby-10.10.1.1 that issue is gone.
>
> Now when I run the same query. I get this error.  I am confused with the
> configuration part here. Could not figure out if there is any mistake in
> creating the Lens metadata.
>
>
>
> *lens-shell>select account_name from account*
>
> Request Id: bdae552e-913c-4733-b924-005948698903
>
> Error Code: 4001
>
> Error Message: Semantic Error : Schema 'MYDB1' does not exist
>
>
>
>
>
> *Here are some of the error logs from lensserver.log*
>
>
>
> 14 Sep 2016 10:21:29 [7491a95f-6665-4d79-9ae6-9b6634ed91b0]
> [grizzly-http-server-0] INFO
> org.apache.lens.server.session.HiveSessionService - Set database to default
> for session e9574f9b-713b-444b-b9fd-ec21b82f6f13
>
> 14 Sep 2016 10:21:29 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO  org.apache.lens.server.AuthenticationFilter -
> Request from user: null , path=queryapi/queries
>
> 14 Sep 2016 10:21:29 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO
> org.apache.lens.server.query.QueryExecutionServiceImpl - ExecuteAsync:
> session:<?xml version="1.0" encoding="UTF-8"
> standalone="yes"?><lensSessionHandle><publicId>e9574f9b-713b-444b-b9fd-ec21b82f6f13</publicId><secretId>ac7e0fc2-00cd-4f00-8042-63678093c8ae</secretId></lensSessionHandle>
> query: select account_name from account
>
> 14 Sep 2016 10:21:30 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO  hive.ql.parse.ParseDriver - Parsing command:
> select account_name from account
>
> 14 Sep 2016 10:21:30 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO  hive.ql.parse.ParseDriver - Parse Completed
>
> 14 Sep 2016 10:21:30 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO
> org.apache.hadoop.hive.metastore.HiveMetaStore - 2: get_table : db=default
> tbl=account
>
> 14 Sep 2016 10:21:30 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO
> org.apache.hadoop.hive.metastore.HiveMetaStore.audit - ugi=pawanm
> ip=unknown-ip-addrcmd=get_table : db=default tbl=account
>
> 14 Sep 2016 10:21:30 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO
> org.apache.hadoop.hive.metastore.HiveMetaStore - 2: Opening raw store with
> implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
>
> 14 Sep 2016 10:21:30 [576876aa-4ace-43e8-a5ac-c9256abc7d98]
> [grizzly-http-server-1] INFO  org.apache.hadoop.hive.metastore.ObjectStore
> - ObjectStore, initialize called
>
> 14 Sep 2016 10:21:30 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  hive.ql.parse.ParseDriver - Parsing command: select account_name from
> account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 5: get_tables:
> db=default pat=.*
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore.audit -
> ugi=pawanm        ip=unknown-ip-addr      cmd=get_tables: db=default pat=.*
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 3: Opening raw store
> with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-1]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore.audit -
> ugi=pawanm        ip=unknown-ip-addr      cmd=get_tables: db=default pat=.*
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-1]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 4: Opening raw store
> with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.ObjectStore - ObjectStore,
> initialize called
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore.audit -
> ugi=pawanm        ip=unknown-ip-addr      cmd=get_tables: db=default pat=.*
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 5: Opening raw store
> with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 3: get_table :
> db=default tbl=account_derby
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore.audit -
> ugi=pawanm        ip=unknown-ip-addr      cmd=get_table : db=default
> tbl=account_derby
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 3: get_table :
> db=default tbl=derbydb
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore.audit -
> ugi=pawanm        ip=unknown-ip-addr      cmd=get_table : db=default
> tbl=derbydb
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore - 3: get_table :
> db=default tbl=derbydb_account_derby
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.hadoop.hive.metastore.HiveMetaStore.audit -
> ugi=pawanm        ip=unknown-ip-addr      cmd=get_table : db=default
> tbl=derbydb_account_derby
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.lens.cube.parse.CandidateTableResolver - Populated
> candidate dims: [account_derby] for account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.cube.parse.JoinResolver - No dimension tables to
> resolve and no join chains present!
>
> *14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.lens.cube.parse.StorageTableResolver - Storage:derbydb is
> not supported*
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-1]
> INFO  org.apache.lens.cube.parse.StorageTableResolver - Storage:derbydb is
> not supported
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.cube.parse.CubeQueryContext - Available candidate
> dims are:[account_derby], picking up account_derby for querying.
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.cube.parse.CubeQueryContext - facts:null,
> dimsToQuery: {account=account_derby}
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.cube.parse.CubeQueryContext - Picked Fact:null
> dimsToQuery: {account=account_derby}
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-3]
> INFO  org.apache.lens.cube.parse.StorageTableResolver - Not considering dim
> table:account_derby as no candidate storage tables eixst
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-1]
> INFO  org.apache.lens.cube.parse.StorageTableResolver - Not considering dim
> table:account_derby as no candidate storage tables eixst
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.server.rewrite.RewriteUtil - Final rewritten query
> for driver: jdbc/jdbc2 is: SELECT ( account . account_name ) FROM
> derbydb_account_derby account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  hive.ql.parse.ParseDriver - Parsing command: SELECT ( account .
> account_name ) FROM derbydb_account_derby account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  hive.ql.parse.ParseDriver - Parse Completed
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.driver.jdbc.JDBCDriver - jdbc/jdbc2 Initialized
> :class org.apache.lens.driver.jdbc.ColumnarSQLRewriter
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  hive.ql.parse.ParseDriver - Parsing command: SELECT ( account .
> account_name ) FROM derbydb_account_derby account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  hive.ql.parse.ParseDriver - Parse Completed
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.driver.jdbc.ColumnarSQLRewriter - @@@Query not
> eligible for inner subquery rewrite
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.driver.jdbc.ColumnarSQLRewriter - Input Query :
> SELECT ( account . account_name ) FROM derbydb_account_derby account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.driver.jdbc.ColumnarSQLRewriter - Rewritten Query :
> select ( dim_account__mydb1_dim_account_account . account_name ) from
> mydb1.dim_account dim_account__mydb1_dim_account_account
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967]
> [MLog-Init-Reporter] INFO  com.mchange.v2.log.MLog - MLog clients using
> slf4j logging.
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  com.mchange.v2.c3p0.C3P0Registry - Initializing c3p0-0.9.5 [built
> 02-January-2015 13:25:04 -0500; debug? true; trace: 10]
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  org.apache.lens.driver.jdbc.DataSourceConnectionProvider - Created
> new datasource for config: jdbcDriverClass:
> org.apache.derby.jdbc.EmbeddedDriver, uri:
> jdbc:derby://apache-lens-2.5.0-beta-bin/server/mydb1, user: null
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> INFO  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initializing
> c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement
> -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000,
> autoCommitOnClose -> false, automaticTestTable -> null,
> breakAfterAcquireFailure -> false, checkoutTimeout -> 10000,
> connectionCustomizerClassName -> null, connectionTesterClassName ->
> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource
> -> caller, dataSourceName -> 2slra99jb5pcl01a8jqat|40209f7c,
> debugUnreturnedConnectionStackTraces -> false, description -> null,
> driverClass -> org.apache.derby.jdbc.EmbeddedDriver, extensions -> {},
> factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false,
> forceUseNamedDriverClass -> false, identityToken ->
> 2slra99jb5pcl01a8jqat|40209f7c, idleConnectionTestPeriod -> 0,
> initialPoolSize -> 3, jdbcUrl ->
> jdbc:derby:/apache-lens-2.5.0-beta-bin/server/mydb1,
> maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 600,
> maxIdleTimeExcessConnections -> 600, maxPoolSize -> 15, maxStatements -> 0,
> maxStatementsPerConnection -> 20, minPoolSize -> 3, numHelperThreads -> 3,
> preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties ->
> {password=******, maxIdleTimeExcessConnections=600,
> maxStatementsPerConnection=20, maxPoolSize=15, maxIdleTime=600,
> checkoutTimeout=10000}, propertyCycle -> 0,
> statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin ->
> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0,
> userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
>
> ……
>
> 14 Sep 2016 10:21:31 [add712a8-caf1-4e3e-a4b7-d38ba4067967] [estimate-2]
> ERROR org.apache.lens.server.api.query.AbstractQueryContext - Setting
> driver cost failed for driver jdbc/jdbc2 Cause: Driver :jdbc/jdbc2 Cause
> :Schema 'MYDB1' does not exist
>
> *org.apache.lens.server.api.error.LensException: SEMANTIC_ERROR[Schema
> 'MYDB1' does not exist]*
>
>         at
> org.apache.lens.driver.jdbc.JDBCDriver.handleJDBCSQLException(JDBCDriver.java:824)
> ~[lens-driver-jdbc-2.5.0-beta.jar:2.5.0-beta]
>
> ….
>
> *Caused by: java.sql.SQLSyntaxErrorException: Schema 'MYDB1' does not
> exist*
>
>         at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source) ~[derby-10.10.1.1.jar:na]
>
>         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source) ~[derby-10.10.1.1.jar:na]
>
> …
>
> Caused by: org.apache.derby.impl.jdbc.EmbedSQLException: Schema 'MYDB1'
> does not exist
>
> …..
>
> Caused by: org.apache.derby.iapi.error.StandardException: Schema 'MYDB1'
> does not exist
>
>         at
> org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> ~[derby-10.10.1.1.jar:na]
>
> ….
>
>
>
>
>
>
>
>
>
>
>
>
>
> *Here is my latest configuration FYR..*
>
>
>
> *<x_storage* classname="org.apache.lens.storage.db.DBStorage"
> name="derbydb" xmlns="uri:lens:cube:0.1"
>
>   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd ">
>
>   <properties>
>
>     <property name="lens.storage.db.url"
> value="jdbc:derby:/apache-lens-2.5.0-beta-bin/server/mydb1"/>
>
>   </properties>
>
> </x_storage>
>
>
>
> *lens-shell>! grep -A 33333333 <x_ examples/resources/derby_account.xml*
>
> command is:grep -A 33333333 <x_ examples/resources/derby_account.xml
>
> *<x_dimension* name="account" xmlns="uri:lens:cube:0.1" xmlns:xsi="
> http://www.w3.org/2001/XMLSchema-instance"
>
>   xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd ">
>
>   <attributes>
>
>     <dim_attribute name="id" _type="INT"/>
>
>    <dim_attribute name="account_name" _type="STRING"/>
>
>     <dim_attribute name="status" _type="STRING"/>
>
>     <dim_attribute name="enabled" _type="INT" />
>
>   </attributes>
>
> </x_dimension>
>
>
>
>
>
> *<x_dimension_table* xmlns="uri:lens:cube:0.1" dimension_name="account"
> table_name="account_derby" weight="0.1"
>
>   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd ">
>
>
>
>     <columns>
>
>         <column name="id" _type="int" comment=""/>
>
>         <column name="account_name" _type="string" comment=""/>
>
>         <column name="status" _type="string" comment=""/>
>
>         <column name="enabled" _type="boolean" comment=""/>
>
>     </columns>
>
>     <storage_tables>
>
>         <storage_table>
>
>             <storage_name>derbydb</storage_name>
>
>             <table_desc external="true"
> input_format="org.apache.hadoop.mapred.TextInputFormat"
> output_format="org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
> serde_class_name="org.apache.lens.storage.db.DBSerde"
> storage_handler_name="org.apache.lens.storage.db.DBStorageHandler"  >
>
>                 <table_parameters>
>
>                     <property name="lens.metastore.native.table.name"
> value="dim_account"/>
>
>                     <property name="lens.metastore.native.db.name"
> value="mydb1"/>
>
>                 </table_parameters>
>
>             </table_desc>
>
>         </storage_table>
>
>     </storage_tables>
>
> </x_dimension_table>
>
>
>
>
>
>
>
> *jdbcdriver-site.xml*
>
> <?xml version="1.0"?>
>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
>
>
> <configuration>
>
>   <property>
>
>     <name>lens.driver.jdbc.driver.class</name>
>
>     <value>org.apache.derby.jdbc.EmbeddedDriver</value>
>
>   </property>
>
>   <property>
>
>     <name>lens.driver.jdbc.db.uri</name>
>
>     <value>jdbc:derby:/apache-lens-2.5.0-beta-bin/server/mydb1</value>
>
>   </property>
>
>   <property>
>
>     <name>lens.cube.query.driver.supported.storages</name>
>
>     <value>derbydb</value>
>
>     <final>true</final>
>
>   </property>
>
>   <property>
>
>     <name>lens.driver.jdbc.query.rewriter</name>
>
>     <value>org.apache.lens.driver.jdbc.ColumnarSQLRewriter</value>
>
>   </property>
>
>   <property>
>
>     <name>lens.driver.jdbc.explain.keyword</name>
>
>     <value>explain plan for </value>
>
>   </property>
>
> </configuration>
>
>
>
>
>
> *Regards*
>
> *Pawan Malwal*
>
>
>
>
>
>
>
> *From:* Puneet Gupta [mailto:puneet.gupta@inmobi.com]
> *Sent:* 13 September 2016 10:30
>
>
> *To:* user@lens.apache.org
> *Cc:* Sharanabasappa G Keriwaddi <sharan@huawei.com>
> *Subject:* Re: Trying to add Derby DB as a Lens data source.
>
>
>
> Hi Pawan
>
>
>
> can you please recheck your configuration in JDBC driver.  Please make
> sure you have chosen the right driver and uri for embedded/server mode
> operation of Derby DB.
>
>
>
> https://db.apache.org/derby/papers/DerbyTut/embedded_intro.html
>
> https://db.apache.org/derby/papers/DerbyTut/ns_intro.html
>
>
>
> Thanks,
>
> Puneet Gupta
>
>
>
> On Mon, Sep 12, 2016 at 5:14 PM, Pawan Malwal <pawan.malwal@huawei.com>
> wrote:
>
> Hi Rajat,
>
>
>
> I tried running the query on dimension. It gives me below error:
>
>
>
> lens-shell>select account_name from account
>
> Request Id: 1891eb0e-5f0a-47ca-a10f-8ba699ec0259
>
> Error Code: 4002
>
> Error Message: Driver Execution Error : A client timed out while waiting
> to acquire a resource from
> com.mchange.v2.resourcepool.BasicResourcePool@5d754b10 -- timeout at
> awaitAvailable()
>
>
>
> Regards
>
> Pawan Malwal
>
>
>
> *From:* Pawan Malwal [mailto:pmalwal1981@gmail.com]
> *Sent:* 09 September 2016 13:03
> *To:* user@lens.apache.org
> *Cc:* Sharanabasappa G Keriwaddi <sharan@huawei.com>
> *Subject:* Re: Trying to add Derby DB as a Lens data source.
>
>
>
> Got it. Thanks so much.
>
>
>
> Regards
>
> Pawan Malwal
>
>
>
> On Fri, Sep 9, 2016 at 12:57 PM, Rajat Khandelwal <rajatgupta59@gmail.com>
> wrote:
>
> Yes, exporting into LENSCPPATH is the way to do this. You can edit
> lens-env.sh and add the export line there, or you can rely on bash sessions
> to have that environment variable.
>
>
>
> On Fri, Sep 9, 2016 at 12:55 PM Pawan Malwal <pmalwal1981@gmail.com>
> wrote:
>
> I did it y'day after creating the dimension and I was getting the blank
> file created in the output folder. But for all the other dim tables created
> by run-examples.sh, such queries generates some data in the output files.
>
> I will do it again and let you know the results by Monday as I do not have
> access to the server from home.
>
>
>
> Meanwhile if you can please reply to this question. Just to confirm.. :)
>
>
>
> Could you please let me know where do we need to have the DB jar files. I
> am not sure if I have kept the Derydb jar file at the right place. Actually
> I just included the .jar file path in the CLASSPATH variable. Is this the
> correct way.?
>
>
>
> Regards
>
> Pawan Malwal
>
>
>
> On Fri, Sep 9, 2016 at 12:35 PM, Rajat Khandelwal <rajatgupta59@gmail.com>
> wrote:
>
> Apologies, I totally missed step 1.
>
>
>
> Can you try running a query on dimension? e.g.
>
>
>
> select account_name from account
>
>
>
>
>
>
>
> On Fri, Sep 9, 2016 at 12:22 PM Pawan Malwal <pmalwal1981@gmail.com>
> wrote:
>
> Hello Rajat,
>
>
>
> Thanks for your reply. Please see my comments inline:
>
> Also could you please let me know where do we need to have the DB jar
> files. I am not sure if I have kept the Derydb jar file at the right place.
> Actually I just included the .jar file path in the CLASSPATH variable. Is
> this the correct way.?
>
>
>
> 1. You haven't created the dimension. You have to create manually.
>
> *Pawan>* I figured it out later and created the dimension for it.
>
>
>
>
>
> 2. The JDBC Driver has to have derbystorage in its supported storages.
>
> *Pawan> *I guess. its alreay there as mentioned in Step1 and Step4. Is
> this what you are pointing out or there is any other place I need to
> mention it in supported storages.?
>
>
>
> *Step1: Created a jdbc driver as follows:*
>
>
>
> <?xml version="1.0"?>
>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
> .......
>
> .....
>
>   <property>
>
>     <name>lens.cube.query.driver.supported.storages</name>
>
>     <value>derbydb</value>
>
>     <final>true</final>
>
> *......*
>
> *...*
>
>
>
> *....*
>
>
>
> *..*
>
>
>
> *Step 4: Added a dimension table:*
>
>
>
> <storage_tables>
>
>         <storage_table>
>
>             <storage_name>derbydb</storage_name>
>
>             <table_desc external="true"
> input_format="org.apache.hadoop.mapred.TextInputFormat"
> output_format="org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
> serde_class_name="org.apache.lens.storage.db.DBSerde"
>
> ...
>
> ....
>
>
>
>
>
>
>
> On Fri, Sep 9, 2016 at 11:35 AM, Rajat Khandelwal <rajatgupta59@gmail.com>
> wrote:
>
> Couple of things
>
>
>
> 1. You haven't created the dimension. You have to create manually.
>
> 2. The JDBC Driver has to have derbystorage in its supported storages.
>
>
>
>
>
>
>
> On Wed, Sep 7, 2016 at 7:16 PM Pawan Malwal <pawan.malwal@huawei.com>
> wrote:
>
> Hi Lens Experts,
>
>
>
> I have started exploring apache Lens recently. I am following Apache docs
> and some of the archived mails to analyze its features. I have come across
> an issue while trying to add Derby DB as a data source.
>
> Here are the details of what I have done so far. Please excuse me for
> elaborated mail as I have put most of configurations to provide all the
> information as I suspect something wrong with my configurations here.
>
>
>
> *Step1: Created a jdbc driver as follows:*
>
>
>
> <?xml version="1.0"?>
>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
>
>
> <configuration>
>
>   <property>
>
>     <name>lens.driver.jdbc.driver.class</name>
>
>     <value>org.apache.derby.jdbc.EmbeddedDriver</value>
>
>   </property>
>
>   <property>
>
>     <name>lens.driver.jdbc.db.uri</name>
>
>     <value>jdbc:derby://localhost:1527/derbyDB;create=true</value>
>
>   </property>
>
>   <property>
>
>     <name>lens.cube.query.driver.supported.storages</name>
>
>     <value>derbydb</value>
>
>     <final>true</final>
>
>   </property>
>
>   <property>
>
>     <name>lens.driver.jdbc.query.rewriter</name>
>
>     <value>org.apache.lens.driver.jdbc.ColumnarSQLRewriter</value>
>
>   </property>
>
>   <property>
>
>     <name>lens.driver.jdbc.explain.keyword</name>
>
>     <value>explain plan for </value>
>
>   </property>
>
> </configuration>
>
>
>
> *Step2: Created table in Derby DB:*
>
>
>
> ij> connect 'jdbc:derby:derbyDB';
>
>
>
> ij> create table DIM_ACCOUNT(id int,account_name varchar(40),status
> varchar(40),enabled int);
>
> 0 rows inserted/updated/deleted
>
>
>
> ij> insert into DIM_ACCOUNT values(1,'account1','active',1);
>
> 1 row inserted/updated/deleted
>
>
>
> ij> insert into DIM_ACCOUNT values(2,'account2','Inactive',1);
>
> 1 row inserted/updated/deleted
>
>
>
> ij> select * from DIM_ACCOUNT;
>
> ID         |ACCOUNT_NAME
> |STATUS                                  |ENABLED
>
>
> ---------------------------------------------------------------------------------------------------------
>
> 1          |account1
> |active                                  |1
>
> 2          |account2
> |Inactive                                |1
>
>
>
> 2 rows selected
>
> ij>
>
>
>
> *Step 3: Created the storage file:*
>
>
>
> lens-shell>! grep -A 33333333 <x_ derby-db-storage.xml
>
> command is:grep -A 33333333 <x_ derby-db-storage.xml
>
> <x_storage classname="org.apache.lens.storage.db.DBStorage" name="derbydb"
> xmlns="uri:lens:cube:0.1"
>
>   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd ">
>
>   <properties>
>
>     <property name="lens.storage.db.url"
> value="jdbc:derby://localhost:1527/derbyDB"/>
>
>   </properties>
>
> </x_storage>
>
> lens-shell>create storage examples/resources/derby-db-storage.xml
>
> succeeded
>
>
>
> *Step 4: Added a dimension table:*
>
>
>
> lens-shell>! grep -A 33333333 <x_
> examples/resources/derby_account_table.xml
>
> command is:grep -A 33333333 <x_ examples/resources/derby_account_table.xml
>
> <x_dimension_table xmlns="uri:lens:cube:0.1" dimension_name="account"
> table_name="account_derby" weight="0.1">
>
>     <columns>
>
>         <column name="id" _type="int" comment=""/>
>
>         <column name="account_name" _type="string" comment=""/>
>
>         <column name="status" _type="string" comment=""/>
>
>         <column name="enabled" _type="boolean" comment=""/>
>
>     </columns>
>
>     <storage_tables>
>
>         <storage_table>
>
>             <storage_name>derbydb</storage_name>
>
>             <table_desc external="true"
> input_format="org.apache.hadoop.mapred.TextInputFormat"
> output_format="org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
> serde_class_name="org.apache.lens.storage.db.DBSerde"
> storage_handler_name="org.apache.lens.storage.db.DBStorageHandler"  >
>
>                 <table_parameters>
>
>                     <property name="lens.metastore.native.table.name"
> value="dim_account"/>
>
>                     <property name="lens.metastore.native.db.name"
> value="derbyDB"/>
>
>                 </table_parameters>
>
>             </table_desc>
>
>         </storage_table>
>
>     </storage_tables>
>
> </x_dimension_table>
>
>
>
> lens-shell>create dimtable examples/resources/derby_account_table.xml
>
> succeeded
>
>
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>
> *Now, when I am trying to run a query from lens-shell, I am getting below
> error. Also I do not see “account” dimension created above using show
> dimensions command. Not sure if I did anything wrong or missed something.*
>
>
>
>
>
> lens-shell>show storages
>
> cluster1
>
> derbydb
>
> local
>
> mydb
>
>
>
> lens-shell>select * from DIM_ACCOUNT
>
> Request Id: bc846148-e585-43b5-9483-01f2e5c28ea1
>
> Error Code: 4001
>
> Error Message: Semantic Error : user lacks privilege or object not found:
> DIM_ACCOUNT
>
>
>
>
>
> lens-shell>show dimensions
>
> product
>
> sample_dim2
>
> city
>
> sample_db_dim
>
> sample_dim
>
> customer
>
>
>
>
>
> Regards
>
> Pawan Malwal
>
>
>
>
>
>
>
>
>
>
>
> --
>
> Thanks and Regards
> Pawan Malwal
>
>
>
>
>
> --
>
> Thanks and Regards
> Pawan Malwal
>
>
>
>
>
> --
>
> Thanks and Regards
> Pawan Malwal
>
>
>
>
>
> _____________________________________________________________
>
> The information contained in this communication is intended solely for the
> use of the individual or entity to whom it is addressed and others
> authorized to receive it. It may contain confidential or legally privileged
> information. If you are not the intended recipient you are hereby notified
> that any disclosure, copying, distribution or taking any action in reliance
> on the contents of this information is strictly prohibited and may be
> unlawful. If you have received this communication in error, please notify
> us immediately by responding to this email and then delete it from your
> system. The firm is neither liable for the proper and complete transmission
> of the information contained in this communication nor for any delay in its
> receipt.
>

Mime
View raw message