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:[email protected]] Sent: 09 September 2016 13:03 To: [email protected] Cc: Sharanabasappa G Keriwaddi <[email protected]> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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<http://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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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<http://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<http://lens.metastore.native.table.name>" value="dim_account"/> <property name="lens.metastore.native.db.name<http://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
