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 >
