Hi all, I think it's time to ask for some help on this, after 3 days of tries and extensive search on the web.
Long story short:
FACTS
----------
- Pyspark with iPython
- version 1.5.0-cdh5.5.1
- I have 2 simple (test) partitioned tables. One external, one managed
- If I query them via Impala or Hive I can see the data. No errors
- If I try to create a Dataframe out of them, no errors. But the Column Values are NULL, except from the "partitioning" column which appears to be correct. Well, behaviour is slightly different according to how I create the Table. More on this below...
HOW I CREATED THE TABLES
-------------------------------------------
- Created both of them using Impala
Table 1:
CREATE EXTERNAL TABLE mn.opt_tbl_blade
PARTITIONED BY (st_insdt string)
STORED AS AVRO
LOCATION 'hdfs:///user/hive/mn/opt_tbl_blade'
TBLPROPERTIES ('avro.schema.url'='hdfs://172.16.0.10/user/mn/SCHEMAS/opt_tbl_blade/opt_tbl_blade.avsc');
ALTER TABLE mn.opt_tbl_blade ADD PARTITION (st_insdt="2008-02");
Table 2:
create table mn.logs (field1 string, field2 string, field3 string)
partitioned by (year string, month string , day string, host string)
row format delimited fields terminated by ',';
HOW I PUT THE DATA INTO THE TABLES
---------------------------------------------------------
Table 1 (EXTERNAL):
- I just moved the Avro files obtained using Sqoop under the "st_insdt=2008-02" Directory in HDFS
- I repeat: I can query the Data from Hue (Impala, Hive)
Table 2 (MANAGED):
insert into logs partition (year="2013", month="07", day="28", host="host1") values ("foo","foo","foo");
insert into logs partition (year="2013", month="07", day="28", host="host2") values ("foo","foo","foo");
insert into logs partition (year="2013", month="07", day="29", host="host1") values ("foo","foo","foo");
insert into logs partition (year="2013", month="07", day="29", host="host2") values ("foo","foo","foo");
insert into logs partition (year="2013", month="08", day="01", host="host1") values ("foo","foo","foo");
- Also in this case, a simple query "select * from logs" gives me the right results!
NOW LET'S LAUNCH PYSPARK AND:
----------------------------------------------------
- Regarding Table 1 (EXTERNAL):
In [1]: blade_DF = sqlContext.table("mn.opt_tbl_blade")
In [2]: blade_DF.printSchema()
root
|-- id: long (nullable = true)
|-- name: string (nullable = true)
|-- descr: string (nullable = true)
|-- thickness: double (nullable = true)
|-- flg_active: string (nullable = true)
|-- flg_delete: string (nullable = true)
|-- st_ins_date: string (nullable = true)
|-- st_upd_date: string (nullable = true)
|-- st_created_by: long (nullable = true)
|-- st_last_upd_by: long (nullable = true)
|-- st_last_upd_oslogin: string (nullable = true)
|-- st_insdt: string (nullable = true)
In [2]: blade_DF.count()
Out[3]: 6
In [4]: blade_DF.show()
+----+----+-----+---------+----------+----------+-----------+-----------+-------------+--------------+-------------------+--------+
| id|name|descr|thickness|flg_active|flg_delete|st_ins_date|st_upd_date|st_created_by|st_last_upd_by|st_last_upd_oslogin|st_insdt|
+----+----+-----+---------+----------+----------+-----------+-----------+-------------+--------------+-------------------+--------+
|null|null| null| null| null| null| null| null| null| null| null| 2008-02|
|null|null| null| null| null| null| null| null| null| null| null| 2008-02|
|null|null| null| null| null| null| null| null| null| null| null| 2008-02|
|null|null| null| null| null| null| null| null| null| null| null| 2008-02|
|null|null| null| null| null| null| null| null| null| null| null| 2008-02|
|null|null| null| null| null| null| null| null| null| null| null| 2008-02|
+----+----+-----+---------+----------+----------+-----------+-----------+-------------+--------------+-------------------+--------+As you can see, the Rows are somehow "sensed", as the number is correct (6 records) and the last field on the right (the Partitioning Field) is correct (this table has just one partition).
But all the fields are NULL. This is definitely not true, and it's not what I see from a "select * from opt_tbl_blade"!!
- Regarding Table 2 (MANAGED):
In [5]: logs_DF = sqlContext.table("mn.logs")
In [6]: logs_DF.show()
+-----------+------+------+----+-----+---+-----+
| field1|field2|field3|year|month|day| host|
+-----------+------+------+----+-----+---+-----+
|foo,foo,foo| null| null|2013| 07| 29|host1|
|foo,foo,foo| null| null|2013| 08| 01|host1|
|foo,foo,foo| null| null|2013| 07| 29|host2|
|foo,foo,foo| null| null|2013| 07| 28|host2|
|foo,foo,foo| null| null|2013| 07| 28|host1|
+-----------+------+------+----+-----+---+-----+This is maybe even more strange. The 3 Fields are "compacted" into one, with jiust one value and a comma separator. But if I run a plain "select * from mn.logs" I see just one "foo" string in each Field (field1, field2, field3)!!
I'm strongly suspecting I'm doing something wrong with field definitions/separators, but as I said in the beginning after extensive search and tries I did not succeed.
BUT: Please note that IF THE TABLE IS NOT PARTITIONED, IT CAN BE AVRO, IT CAN BE MANAGED, IT CAN BE WATHEVER... Results are correct!!!
Can anybody with fresh eyes give me some suggestions please?
Thanks in advance for any help...
