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 >
