You need to explode only once (in conjunction with LATERAL VIEW). After exploding you can use a new column (called prod_and_ts in my example) which will be of struct type. Then, you can resolve the product_id and timestamps members of this new struct column to retrieve the desired result. SELECT user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps … Read more
You can use below statement to write the contents of dataframe in CSV format df.write.csv(“/data/home/csv”) If you need to write the whole dataframe into a single CSV file, then use df.coalesce(1).write.csv(“/data/home/sample.csv”) For spark 1.x, you can use spark-csv to write the results into CSV files Below scala snippet would help import org.apache.spark.sql.hive.HiveContext // sc – … Read more
cast(str_column as int) From: Language manual UDFs – type conversion functions
There are a few efficient ways to implement this. Let’s start with required imports: from pyspark.sql.functions import col, expr, when You can use Hive IF function inside expr: new_column_1 = expr( “””IF(fruit1 IS NULL OR fruit2 IS NULL, 3, IF(fruit1 = fruit2, 1, 0))””” ) or when + otherwise: new_column_2 = when( col(“fruit1”).isNull() | col(“fruit2”).isNull(), … Read more
Some of the answers here are out of date as of Hive 0.14 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingvaluesintotablesfromSQL It is now possible to insert using syntax such as: CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2)); INSERT INTO TABLE students VALUES (‘fred flintstone’, 35, 1.28), (‘barney rubble’, 32, 2.32);
DESCRIBE FORMATTED my_table; or DESCRIBE FORMATTED my_table PARTITION (my_column=’my_value’);
According to the LanguageManual, you can use unix_timestamp() to get the “current time stamp using the default time zone.” If you need to convert that to something more human-readable, you can use from_unixtime(unix_timestamp()).
Although it is possible to use INSERT OVERWRITE to get data out of Hive, it might not be the best method for your particular case. First let me explain what INSERT OVERWRITE does, then I’ll describe the method I use to get tsv files from Hive tables. According to the manual, your query will store … Read more
You can update a Hive partition by, for example: ALTER TABLE logs PARTITION(year = 2012, month = 12, day = 18) SET LOCATION ‘hdfs://user/darcy/logs/2012/12/18’; This command does not move the old data, nor does it delete the old data. It simply sets the partition to the new location. To drop a partition, you can do … Read more
As of Hive 0.10 this patch-967 implements SHOW CREATE TABLE which “shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view.” Usage: SHOW CREATE TABLE myTable;