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]> 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]> > 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 <[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]> >>> 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
