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.mal...@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:pmalwal1...@gmail.com] > *Sent:* 09 September 2016 13:03 > *To:* user@lens.apache.org > *Cc:* Sharanabasappa G Keriwaddi <sha...@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 <rajatgupt...@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 <pmalwal1...@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 <rajatgupt...@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 <pmalwal1...@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 <rajatgupt...@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.mal...@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.
