Try this,
hive> create table default.foo(id int) clustered by (id) into 2 buckets
STORED AS ORC TBLPROPERTIES ('transactional'='true');
hive> insert into default.foo values(10);
scala> sqlContext.table("default.foo").count // Gives 0, which is wrong
because data is still in delta files
Now run major compaction:
hive> ALTER TABLE default.foo COMPACT 'MAJOR';
scala> sqlContext.table("default.foo").count // Gives 1
hive> insert into foo values(20);
scala> sqlContext.table("default.foo").count* // Gives 2 , no compaction
required.*
Regards
Sanjiv Singh
Mob : +091 9990-447-339
On Tue, Feb 23, 2016 at 2:02 PM, Varadharajan Mukundan <srinath...@gmail.com
> wrote:
> This is the scenario i'm mentioning.. I'm not using Spark JDBC. Not sure
> if its different.
>
> Please walkthrough the below commands in the same order to understand the
> sequence.
>
> hive> create table default.foo(id int) clustered by (id) into 2 buckets
> STORED AS ORC TBLPROPERTIES ('transactional'='true');
> hive> insert into foo values(10);
>
> scala> sqlContext.table("default.foo").count // Gives 0, which is wrong
> because data is still in delta files
>
> Now run major compaction:
>
> hive> ALTER TABLE default.foo COMPACT 'MAJOR';
>
> scala> sqlContext.table("default.foo").count // Gives 1
>
>
> On Tue, Feb 23, 2016 at 12:35 PM, @Sanjiv Singh <sanjiv.is...@gmail.com>
> wrote:
>
>> Hi Varadharajan,
>>
>>
>> That is the point, Spark SQL is able to recognize delta files. See below
>> directory structure, ONE BASE (43 records) and one DELTA (created after
>> last insert). And I am able see last insert through Spark SQL.
>>
>>
>> *See below complete scenario :*
>>
>> *Steps:*
>>
>> - Inserted 43 records in table.
>> - Run major compaction on table.
>> - *alter table mytable COMPACT 'major';*
>> - Disabled auto compaction on table.
>> - *alter table mytable set
>> TBLPROPERTIES("NO_AUTO_COMPACTION"="true");*
>> - Inserted 1 record in table.
>>
>>
>> > *hadoop fs -ls /apps/hive/warehouse/mydb.db/mytable*
>> drwxrwxrwx - root hdfs 0 2016-02-23 11:43
>> /apps/hive/warehouse/mydb.db/mytable/base_0000087
>> drwxr-xr-x - root hdfs 0 2016-02-23 12:02
>> /apps/hive/warehouse/mydb.db/mytable/delta_0000088_0000088
>>
>> *SPARK JDBC :*
>>
>> 0: jdbc:hive2://myhost:9999> select count(*) from mytable ;
>> +------+
>> | _c0 |
>> +------+
>> | 44 |
>> +------+
>> 1 row selected (1.196 seconds)
>>
>> *HIVE JDBC :*
>>
>> 1: jdbc:hive2://myhost:10000> select count(*) from mytable ;
>> +------+--+
>> | _c0 |
>> +------+--+
>> | 44 |
>> +------+--+
>> 1 row selected (0.121 seconds)
>>
>>
>> Regards
>> Sanjiv Singh
>> Mob : +091 9990-447-339
>>
>> On Tue, Feb 23, 2016 at 12:04 PM, Varadharajan Mukundan <
>> srinath...@gmail.com> wrote:
>>
>>> Hi Sanjiv,
>>>
>>> Yes.. If we make use of Hive JDBC we should be able to retrieve all the
>>> rows since it is hive which processes the query. But i think the problem
>>> with Hive JDBC is that there are two layers of processing, hive and then at
>>> spark with the result set. And another one is performance is limited to
>>> that single HiveServer2 node and network.
>>>
>>> But If we make use of sqlContext.table function in spark to access hive
>>> tables, it is supposed to read files directly from HDFS skipping the hive
>>> layer. But it doesn't read delta files and just reads the contents from
>>> base folder. Only after Major compaction, the delta files would be merged
>>> with based folder and be visible for Spark SQL
>>>
>>> On Tue, Feb 23, 2016 at 11:57 AM, @Sanjiv Singh <sanjiv.is...@gmail.com>
>>> wrote:
>>>
>>>> Hi Varadharajan,
>>>>
>>>> Can you elaborate on (you quoted on previous mail) :
>>>> "I observed that hive transaction storage structure do not work with
>>>> spark yet"
>>>>
>>>>
>>>> If it is related to delta files created after each transaction and
>>>> spark would not be able recognize them. then I have a table *mytable *(ORC
>>>> , BUCKETED , NON-SORTED) , already done lots on insert , update and
>>>> deletes. I can see delta files created in HDFS (see below), Still able to
>>>> fetch consistent records through Spark JDBC and HIVE JDBC.
>>>>
>>>> Not compaction triggered for that table.
>>>>
>>>> > *hadoop fs -ls /apps/hive/warehouse/mydb.db/mytable*
>>>>
>>>> drwxrwxrwx - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/base_0000060
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000061_0000061
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000062_0000062
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000063_0000063
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000064_0000064
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000065_0000065
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000066_0000066
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000067_0000067
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000068_0000068
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000069_0000069
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000070_0000070
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000071_0000071
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:38
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000072_0000072
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000073_0000073
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000074_0000074
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000075_0000075
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000076_0000076
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000077_0000077
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000078_0000078
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000079_0000079
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000080_0000080
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000081_0000081
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000082_0000082
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000083_0000083
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000084_0000084
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:39
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000085_0000085
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:40
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000086_0000086
>>>> drwxr-xr-x - root hdfs 0 2016-02-23 11:41
>>>> /apps/hive/warehouse/mydb.db/mytable/delta_0000087_0000087
>>>>
>>>>
>>>>
>>>> Regards
>>>> Sanjiv Singh
>>>> Mob : +091 9990-447-339
>>>>
>>>> On Mon, Feb 22, 2016 at 1:38 PM, Varadharajan Mukundan <
>>>> srinath...@gmail.com> wrote:
>>>>
>>>>> Actually the auto compaction if enabled is triggered based on the
>>>>> volume of changes. It doesn't automatically run after every insert. I
>>>>> think
>>>>> its possible to reduce the thresholds but that might reduce performance by
>>>>> a big margin. As of now, we do compaction after the batch insert
>>>>> completes.
>>>>>
>>>>> The only other way to solve this problem as of now is to use Hive JDBC
>>>>> API.
>>>>>
>>>>> On Mon, Feb 22, 2016 at 11:39 AM, @Sanjiv Singh <
>>>>> sanjiv.is...@gmail.com> wrote:
>>>>>
>>>>>> Compaction would have been triggered automatically as following
>>>>>> properties already set in *hive-site.xml*. and also
>>>>>> *NO_AUTO_COMPACTION* property not been set for these tables.
>>>>>>
>>>>>>
>>>>>> <property>
>>>>>>
>>>>>> <name>hive.compactor.initiator.on</name>
>>>>>>
>>>>>> <value>true</value>
>>>>>>
>>>>>> </property>
>>>>>>
>>>>>> <property>
>>>>>>
>>>>>> <name>hive.compactor.worker.threads</name>
>>>>>>
>>>>>> <value>1</value>
>>>>>>
>>>>>> </property>
>>>>>>
>>>>>>
>>>>>> Documentation is upset sometimes.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regards
>>>>>> Sanjiv Singh
>>>>>> Mob : +091 9990-447-339
>>>>>>
>>>>>> On Mon, Feb 22, 2016 at 9:49 AM, Varadharajan Mukundan <
>>>>>> srinath...@gmail.com> wrote:
>>>>>>
>>>>>>> Yes, I was burned down by this issue couple of weeks back. This also
>>>>>>> means that after every insert job, compaction should be run to access
>>>>>>> new
>>>>>>> rows from Spark. Sad that this issue is not documented / mentioned
>>>>>>> anywhere.
>>>>>>>
>>>>>>> On Mon, Feb 22, 2016 at 9:27 AM, @Sanjiv Singh <
>>>>>>> sanjiv.is...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Hi Varadharajan,
>>>>>>>>
>>>>>>>> Thanks for your response.
>>>>>>>>
>>>>>>>> Yes it is transnational table; See below *show create table. *
>>>>>>>>
>>>>>>>> Table hardly have 3 records , and after triggering minor compaction
>>>>>>>> on tables , it start showing results on spark SQL.
>>>>>>>>
>>>>>>>>
>>>>>>>> > *ALTER TABLE hivespark COMPACT 'major';*
>>>>>>>>
>>>>>>>>
>>>>>>>> > *show create table hivespark;*
>>>>>>>>
>>>>>>>> CREATE TABLE `hivespark`(
>>>>>>>>
>>>>>>>> `id` int,
>>>>>>>>
>>>>>>>> `name` string)
>>>>>>>>
>>>>>>>> CLUSTERED BY (
>>>>>>>>
>>>>>>>> id)
>>>>>>>>
>>>>>>>> INTO 32 BUCKETS
>>>>>>>>
>>>>>>>> ROW FORMAT SERDE
>>>>>>>>
>>>>>>>> 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
>>>>>>>>
>>>>>>>> STORED AS INPUTFORMAT
>>>>>>>>
>>>>>>>> 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
>>>>>>>>
>>>>>>>> OUTPUTFORMAT
>>>>>>>>
>>>>>>>> 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
>>>>>>>>
>>>>>>>> LOCATION
>>>>>>>>
>>>>>>>> 'hdfs://myhost:8020/apps/hive/warehouse/mydb.db/hivespark'
>>>>>>>> TBLPROPERTIES (
>>>>>>>>
>>>>>>>> 'COLUMN_STATS_ACCURATE'='true',
>>>>>>>>
>>>>>>>> 'last_modified_by'='root',
>>>>>>>>
>>>>>>>> 'last_modified_time'='1455859079',
>>>>>>>>
>>>>>>>> 'numFiles'='37',
>>>>>>>>
>>>>>>>> 'numRows'='3',
>>>>>>>>
>>>>>>>> 'rawDataSize'='0',
>>>>>>>>
>>>>>>>> 'totalSize'='11383',
>>>>>>>>
>>>>>>>> 'transactional'='true',
>>>>>>>>
>>>>>>>> 'transient_lastDdlTime'='1455864121') ;
>>>>>>>>
>>>>>>>>
>>>>>>>> Regards
>>>>>>>> Sanjiv Singh
>>>>>>>> Mob : +091 9990-447-339
>>>>>>>>
>>>>>>>> On Mon, Feb 22, 2016 at 9:01 AM, Varadharajan Mukundan <
>>>>>>>> srinath...@gmail.com> wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> Is the transaction attribute set on your table? I observed that
>>>>>>>>> hive transaction storage structure do not work with spark yet. You can
>>>>>>>>> confirm this by looking at the transactional attribute in the output
>>>>>>>>> of
>>>>>>>>> "desc extended <tablename>" in hive console.
>>>>>>>>>
>>>>>>>>> If you'd need to access transactional table, consider doing a
>>>>>>>>> major compaction and then try accessing the tables
>>>>>>>>>
>>>>>>>>> On Mon, Feb 22, 2016 at 8:57 AM, @Sanjiv Singh <
>>>>>>>>> sanjiv.is...@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> I have observed that Spark SQL is not returning records for hive
>>>>>>>>>> bucketed ORC tables on HDP.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On spark SQL , I am able to list all tables , but queries on hive
>>>>>>>>>> bucketed tables are not returning records.
>>>>>>>>>>
>>>>>>>>>> I have also tried the same for non-bucketed hive tables. it is
>>>>>>>>>> working fine.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Same is working on plain Apache setup.
>>>>>>>>>>
>>>>>>>>>> Let me know if needs other details.
>>>>>>>>>>
>>>>>>>>>> Regards
>>>>>>>>>> Sanjiv Singh
>>>>>>>>>> Mob : +091 9990-447-339
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Thanks,
>>>>>>>>> M. Varadharajan
>>>>>>>>>
>>>>>>>>> ------------------------------------------------
>>>>>>>>>
>>>>>>>>> "Experience is what you get when you didn't get what you wanted"
>>>>>>>>> -By Prof. Randy Pausch in "The Last Lecture"
>>>>>>>>>
>>>>>>>>> My Journal :- http://varadharajan.in
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Thanks,
>>>>>>> M. Varadharajan
>>>>>>>
>>>>>>> ------------------------------------------------
>>>>>>>
>>>>>>> "Experience is what you get when you didn't get what you wanted"
>>>>>>> -By Prof. Randy Pausch in "The Last Lecture"
>>>>>>>
>>>>>>> My Journal :- http://varadharajan.in
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Thanks,
>>>>> M. Varadharajan
>>>>>
>>>>> ------------------------------------------------
>>>>>
>>>>> "Experience is what you get when you didn't get what you wanted"
>>>>> -By Prof. Randy Pausch in "The Last Lecture"
>>>>>
>>>>> My Journal :- http://varadharajan.in
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Thanks,
>>> M. Varadharajan
>>>
>>> ------------------------------------------------
>>>
>>> "Experience is what you get when you didn't get what you wanted"
>>> -By Prof. Randy Pausch in "The Last Lecture"
>>>
>>> My Journal :- http://varadharajan.in
>>>
>>
>>
>
>
> --
> Thanks,
> M. Varadharajan
>
> ------------------------------------------------
>
> "Experience is what you get when you didn't get what you wanted"
> -By Prof. Randy Pausch in "The Last Lecture"
>
> My Journal :- http://varadharajan.in
>
