Storing data in Hive is nothing but storing data in HDFS ( hadoop ). Just look in /apps/hive/warhouse/databasename/tablename Hive is very simple that way. No magical tablespaces or other things. A table is just a folder in HDFS with some files in it and an entry in the metastore telling hive what format the data in that folder has. So Using ORC in Hive is the same as storing ORC in HDFS. So yes make an external table on the tbl file and transform it into an ORC table. All good. ... View more

Use ORC in HDP ( and parquet in CDH ). They are much much faster than text. Spark also supports reading from ORC. Some tips on how to get the best performance out of your table setup ( partitioning, predicate pushdown ... ) http://www.slideshare.net/BenjaminLeonhardi/hive-loading-data Oh and finally if you want to use mostly SQL then Hive on Tez with all new features ( CBO, Vectorization, ORC, ... ) beats the pants of Spark no questions asked. Spark is cool if you want to do something like Streaming, Data mining do some programming style data transformation or your data sets are small and you can pin them in memory for repeated queries. ... View more

1) You essentially have two options. Use Sqoop import-all-tables with exclude as you mention. However in that case you have a single sqoop action in oozie and no parallelity in oozie. However sqoop might provide that. You have some limitations though ( only straight imports all columns , ... ) Alternatively you make an oozie flow that uses a fork and then one single table sqoop action per table. In that case you have fine grained control over how much you want to run in parallel. ( You could for example load 4 at a time by doing Start -> Fork -> 4 Sqoop Actions -> Join -> Fork -> 4 Sqoop Actions -> Join -> End 2) If you want incremental load I don't think the Sqoop import-all-tables is possible. So one Sqoop action per table it is. Essentially you can either use Sqoop incremental import functionality ( using a property file ) or use WHERE conditions and give through the date parameter from the coordinator. You can use coord:dateformat to transform your execution date. 3) Run One coord for each table OR have a Decision action in the oozie workflow that skips some sqoop actions Like Start -> Sqoop1 where date = mydate -> Decision if mydate % 3 = 0 then Sqoop2 else end. 4) incremental imports load the new data into a folder in HDFS. If you run it the folder needs to be deleted. If you use append it doesn't delete the old data in HDFS. Now you may ask why would I ever not want append and the reason is that you mostly do something with the data after like importing the new data to a hive partitioned table. If you would use append he would load the same data over and over. ... View more

Essentially hive.server2.tez.default.queues exists for pre initialized Tez sessions. Normally starting an Application Master takes around 10 seconds so the first query will be significantly slow. However you can set hive.server2.tez.initialize.default.sessions=true. This will initialize hive.server2.tez.sessions.per.default.queue AMs for each of the queues which will then be used for query execution. For most situations I would not bother with it too much since subsequent queries will reuse existing AMs ( which have an idle wait time ). However if you have strong SLAs you may want to use it. the tez.queue.name is then the actual queue you want to execute in. If you hit one of the default queues the AM is already there and everything is faster. You might have distinct queues for big heavy and small interactive queries however you still need to set the queue yourself. ... View more

No you don't have to install a local KDC, you have to configure SSSD to connect to AD for linux user authentication. As said AD normally provides kerberos tickets automatically. To create a new service user in AD you best talk to your AD team. Once you have created a hue service user ( in the same group as the hdfs etc. users ) you should be able to export the keytab. The guide would be for a standard KDC. Which is also an option, however if you want a standard KDC then you need to add a one way trust from the AD to your local KDC. ... View more

Not directly I am afraid. You can write a MapReduce job that transforms them into normal delimited data. Similar to the way it was done with Tika here. ( Assuming you have lots of small files ) https://community.hortonworks.com/repos/4576/apache-tika-integration-with-mapreduce.html You would however need to use a Java library like POI instead of Tika https://poi.apache.org/ To read it directly in Hive you need to write an HiveInputFormat. You can use this inputformat class as an example: https://sreejithrpillai.wordpress.com/2014/11/06/excel-inputformat-for-hadoop-mapreduce/ If you return a row for each record that is delimited and pretend to the Hive Serde that its a text inputformat you might be able to get it working. ... View more

First HDFS, First Hive it doesn't really matter. You can transform your data any way you want after. CTAS tables are great. I personally would use HDFS first because then you can easily use other tools like pig on the raw data as well to do some data checking, cleaning, transformations. Pig can be nice to check formatting issues for example. ( You can for example read delimited data as strings and apply regexes on it easier than you could in hive ). Commonly in Hadoop I would do: - Have a /data/raw/project1/timestamp folder which contains all your raw ingested data and keep it as long as you want. - Run transformation jobs to create clean Hive/ORC tables - Run any aggregation jobs on the ORC tables if possible ( faster ) ( you can for example export those to tableau etc ) For sqoop it doesn't matter so much because the data that comes out of a database is normally already clean so hive directly works well but for other data sources ( flat files ) you normally have some data quality issues that might require some pig or whatever magic. ... View more