Use something like
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<x_storage xmlns="uri:lens:cube:0.1" name="mysql"
classname="org.apache.lens.storage.db.DBStorage">
</x_storage>
<storage_table>
<update_periods>
<update_period>HOURLY</update_period>
</update_periods>
<storage_name>db</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"
num_buckets="0" compressed="false">
<part_cols>
<column name="pt" _type="string" comment="date partition"/>
<column name="dt" _type="string" comment="date partition"/>
<column name="ot" _type="string" comment="date partition"/>
</part_cols>
<table_parameters>
<property name="lens.metastore.native.table.name"
value="table_name_in_mysql_db"/>
<property name="lens.metastore.native.db.name" value="mysql_db_name"/>
<property name="lens.metastore.native.table.column.mapping"
value="pt=process_time,field1=field1_actual_name,field2=field2_actual_name"/>
<property name="cube.storagetable.start.times" value="now - 4 days"/>
</table_parameters>
<time_part_cols>pt</time_part_cols>
<time_part_cols>dt</time_part_cols>
<time_part_cols>ot</time_part_cols>
</table_desc>
</storage_table>
On Sat, Aug 27, 2016 at 3:42 AM Tao Yan <t...@linkedin.com> wrote:
> Hi Lens Developers,
>
> I am trying to add mysql as a data source, so, I created the driver as
> follows:
>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
> <configuration>
> <property>
> <name>lens.driver.jdbc.driver.class</name>
> <value>com.mysql.jdbc.Driver</value>
> </property>
> <property>
> <name>lens.driver.jdbc.db.uri</name>
>
> <value>jdbc:mysql://****mysql**hostname****/lens;user=****;passowrd=****</value>
> </property>
> <property>
> <name>lens.driver.jdbc.db.user</name>
> <value>****</value>
> </property>
> <property>
> <name>lens.cube.query.driver.supported.storages</name>
> <value>mysql</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>
>
> And created a test table with data in mysql:
>
> create table dimension1_subset (
> primary_key BIGINT(8) not null primary key,
> attr1 BIGINT(8),
> attr5 BIGINT(8)
> );
>
> [image: Screen Shot 2016-08-26 at 2.44.24 PM.png]
>
> And created the storage file:
>
> <?xml version="1.0" encoding="UTF-8"?>
>
> <x_storage classname="org.apache.lens.storage.db.DBStorage" name="mysql"
> 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:mysql://lva1-db34.corp.linkedin.com/lens
> <http://lva1-db34.corp.linkedin.com/lens>"/>*
> </properties>
> </x_storage>
>
> I am confused when set the value for *lens.storage.db.url *because* it is
> neither local filesystem nor HDFS.*
>
> And I don't know what should the storage table look like:
>
> <x_dimension_table dimension_name="dimension1"
> table_name="dimension1_table3" weight="5.0" 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 ">
> <columns>
> <column name="primary_key" _type="BIGINT"/>
> <column name="attr1" _type="BIGINT"/>
> <column name="attr5" _type="BIGINT"/>
> </columns>
> <properties>
> <property name="dimension1.prop" value="t3"/>
> </properties>
> <storage_tables>
>
>
> * <storage_table> ... </storage_table>*
> </storage_tables>
> </x_dimension_table>
>
> What should I use for the table_location, and what about part_cols? How do
> I add data to this table?
>
> Thanks,
> --
>
> *Tao Yan*
> Software Engineer
> Data Analytics Infrastructure Tools and Services
>
>
>
> 206.250.5345
> t...@linkedin.com
> https://www.linkedin.com/in/taousc
>
