You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by prakash sejwani <pr...@gmail.com> on 2010/03/03 12:05:12 UTC
How to export hive tables into mysql? or How do you translate hive
tables' schema into mysql schema
Hi all,
I have a tables in hive of product_hits and company_hits i want to
export the data to my web application which is build on ruby on rails
framework to show this data in my app.
table description are as follows
-- This table will store the hits (data from the server log) for each
product
CREATE TABLE product_hits (
ip STRING, ident STRING, user STRING, time STRING,
method STRING, resource STRING, protocol STRING, status INT,
length INT, referer STRING, agent STRING,
product_id INT,
company_id INT,
ip_country STRING,
product_name STRING,
company_name STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"
STORED AS TEXTFILE;
-- As above, except for companies
CREATE TABLE company_hits (
ip STRING, ident STRING, user STRING, time STRING,
method STRING, resource STRING, protocol STRING, status INT,
length INT, referer STRING, agent STRING,
company_id INT,
ip_country STRING,
company_name STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"
STORED AS TEXTFILE;
Thanks,
prakash
Re: How to export hive tables into mysql? or How do you translate
hive tables' schema into mysql schema
Posted by Aaron Kimball <aa...@cloudera.com>.
If you use Hadoop trunk, the Sqoop project (see src/contrib/sqoop) will
support exports into databases such as mysql. You basically create the table
in mysql, then run Sqoop on the delimited files in HDFS. It'll run a
MapReduce job to export the files in parallel and insert the rows into the
table.
The next release of Cloudera's Distribution for Hadoop (CDH3) which isn't
out quite just yet will also have this functionality.
Cheers,
- Aaron
On Wed, Mar 3, 2010 at 7:03 AM, Edward Capriolo <ed...@gmail.com>wrote:
> On Wed, Mar 3, 2010 at 6:05 AM, prakash sejwani
> <pr...@gmail.com> wrote:
> > Hi all,
> > I have a tables in hive of product_hits and company_hits i want
> to
> > export the data to my web application which is build on ruby on rails
> > framework to show this data in my app.
> >
> > table description are as follows
> >
> > -- This table will store the hits (data from the server log) for each
> > product
> > CREATE TABLE product_hits (
> > ip STRING, ident STRING, user STRING, time STRING,
> > method STRING, resource STRING, protocol STRING, status INT,
> > length INT, referer STRING, agent STRING,
> > product_id INT,
> > company_id INT,
> > ip_country STRING,
> > product_name STRING,
> > company_name STRING
> > )
> > ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"
> > STORED AS TEXTFILE;
> >
> > -- As above, except for companies
> > CREATE TABLE company_hits (
> > ip STRING, ident STRING, user STRING, time STRING,
> > method STRING, resource STRING, protocol STRING, status INT,
> > length INT, referer STRING, agent STRING,
> > company_id INT,
> > ip_country STRING,
> > company_name STRING
> > )
> > ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"
> > STORED AS TEXTFILE;
> >
> >
> > Thanks,
> > prakash
> >
>
> Take a look at
>
> http://svn.apache.org/viewvc/hadoop/hive/trunk/contrib/src/java/org/apache/hadoop/hive/contrib/genericudf/example/GenericUDFDBOutput.java?revision=907950&view=markup
>
>
> http://svn.apache.org/viewvc/hadoop/hive/trunk/contrib/src/test/queries/clientpositive/dboutput.q?revision=810696&view=markup
>
> UDFDBOutput is a 'quick and dirty' export UDF. It is not going to help
> you with schema issues but I can get data out of hive into a jdbc
> database without a middle man.
>
Re: How to export hive tables into mysql? or How do you translate
hive tables' schema into mysql schema
Posted by Edward Capriolo <ed...@gmail.com>.
On Wed, Mar 3, 2010 at 6:05 AM, prakash sejwani
<pr...@gmail.com> wrote:
> Hi all,
> I have a tables in hive of product_hits and company_hits i want to
> export the data to my web application which is build on ruby on rails
> framework to show this data in my app.
>
> table description are as follows
>
> -- This table will store the hits (data from the server log) for each
> product
> CREATE TABLE product_hits (
> ip STRING, ident STRING, user STRING, time STRING,
> method STRING, resource STRING, protocol STRING, status INT,
> length INT, referer STRING, agent STRING,
> product_id INT,
> company_id INT,
> ip_country STRING,
> product_name STRING,
> company_name STRING
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"
> STORED AS TEXTFILE;
>
> -- As above, except for companies
> CREATE TABLE company_hits (
> ip STRING, ident STRING, user STRING, time STRING,
> method STRING, resource STRING, protocol STRING, status INT,
> length INT, referer STRING, agent STRING,
> company_id INT,
> ip_country STRING,
> company_name STRING
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"
> STORED AS TEXTFILE;
>
>
> Thanks,
> prakash
>
Take a look at
http://svn.apache.org/viewvc/hadoop/hive/trunk/contrib/src/java/org/apache/hadoop/hive/contrib/genericudf/example/GenericUDFDBOutput.java?revision=907950&view=markup
http://svn.apache.org/viewvc/hadoop/hive/trunk/contrib/src/test/queries/clientpositive/dboutput.q?revision=810696&view=markup
UDFDBOutput is a 'quick and dirty' export UDF. It is not going to help
you with schema issues but I can get data out of hive into a jdbc
database without a middle man.