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
