Hi Mich ,
Please find below output of command.
desc formatted tuning_dd_key ;
+-------------------------------+-----------------------------------------------------------------------------------------------+-----------------------+--+
| col_name |
data_type | comment
|
+-------------------------------+-----------------------------------------------------------------------------------------------+-----------------------+--+
| # col_name | data_type
| comment
|
| | NULL
| NULL
|
| m_d_key | smallint
|
|
| sb_gu_key | bigint
|
|
| t_ev_st_dt | date
|
|
| ad_zn_key | int
|
|
| c_dt | date
|
|
| e_p_dt | date
|
|
| sq_nbr | int
|
|
| | NULL
| NULL
|
| # Detailed Table Information | NULL
| NULL
|
| Database: | PRDDB
| NULL
|
| CreateTime: | Thu Jun 23 11:03:53 EDT 2016
| NULL
|
| LastAccessTime: | UNKNOWN
| NULL
|
| Protect Mode: | None
| NULL
|
| Retention: | 0
| NULL
|
| Table Type: | MANAGED_TABLE
| NULL
|
| Table Parameters: | NULL
| NULL
|
| | COLUMN_STATS_ACCURATE
| true
|
| | numFiles
| 256
|
| | numRows
| 6357592675
|
| | rawDataSize
| 0
|
| | totalSize
| 54076898961
|
| | transactional
| true
|
| | transient_lastDdlTime
| 1466694970
|
| | NULL
| NULL
|
| # Storage Information | NULL
| NULL
|
| SerDe Library: | org.apache.hadoop.hive.ql.io.orc.OrcSerde
| NULL
|
| InputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
| NULL |
| OutputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
| NULL |
| Compressed: | No
| NULL
|
| Num Buckets: | 256
| NULL
|
| Bucket Columns: | [sbsc_guid_key, mas_div_key]
| NULL
|
| Sort Columns: | []
| NULL
|
| Storage Desc Params: | NULL
| NULL
|
| | serialization.format
| 1
|
+-------------------------------+-----------------------------------------------------------------------------------------------+-----------------------+--+
Regards
Sanjiv Singh
Mob : +091 9990-447-339
On Thu, Jun 23, 2016 at 12:47 PM, Mich Talebzadeh <[email protected]
> wrote:
> Do you also have the output from
>
> desc formatted tuning_dd_key
>
> and send the output please?
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn *
> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 23 June 2016 at 17:41, @Sanjiv Singh <[email protected]> wrote:
>
>> Hi Gopal,
>>
>> I am using Tez as execution engine.
>>
>> DAG :
>>
>> +--------------------------------------------------------+--+
>> |
>> Explain
>> |
>> +---------------------------------------------------------+--+
>> | Plan not optimized by CBO.
>> |
>> |
>> |
>> | Vertex dependency in root stage
>> |
>> | Reducer 2 <- Map 1 (SIMPLE_EDGE)
>> |
>> |
>> |
>> | Stage-0
>> |
>> | Fetch Operator
>> |
>> | limit:-1
>> |
>> | Stage-1
>> |
>> | Reducer 2
>> |
>> | File Output Operator [FS_55596]
>> |
>> | compressed:false
>> |
>> | Statistics:Num rows: 6357592675 Data size: 54076899328
>> Basic stats: COMPLETE Column stats: NONE |
>> |
>> table:{"serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe","input
>> format:":"org.apache.hadoop.mapred.TextInputFormat","output
>> format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"} |
>> | Select Operator [SEL_55594]
>> |
>> |
>>
>> outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
>> |
>> | Statistics:Num rows: 6357592675 Data size: 54076899328
>> Basic stats: COMPLETE Column stats: NONE |
>> | PTF Operator [PTF_55593]
>> |
>> | Function definitions:[{"Input
>> definition":{"type:":"WINDOWING"}},{"partition by:":"_col0,
>> _col1","name:":"windowingtablefunction","order by:":"_col2"}] |
>> | Statistics:Num rows: 6357592675 Data size:
>> 54076899328 Basic stats: COMPLETE Column stats: NONE |
>> | Select Operator [SEL_55592]
>> |
>> | |
>> outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
>> |
>> | | Statistics:Num rows: 6357592675 Data size:
>> 54076899328 Basic stats: COMPLETE Column stats: NONE |
>> | |<-Map 1 [SIMPLE_EDGE] vectorized
>> |
>> | Reduce Output Operator [RS_55597]
>> |
>> | key expressions:m_d_key (type: smallint),
>> sb_gu_key (type: bigint), t_ev_st_dt (type: date) |
>> | Map-reduce partition columns:m_d_key (type:
>> smallint), sb_gu_key (type: bigint) |
>> | sort order:+++
>> |
>> | Statistics:Num rows: 6357592675 Data size:
>> 54076899328 Basic stats: COMPLETE Column stats: NONE
>> |
>> | value expressions:ad_zn_key (type: int), c_dt
>> (type: date), e_p_dt (type: date), sq_nbr (type: int) |
>> | TableScan [TS_55590]
>> |
>> | ACID table:true
>> |
>> | alias:tuning_dd_key
>> |
>> | Statistics:Num rows: 6357592675 Data size:
>> 54076899328 Basic stats: COMPLETE Column stats: NONE |
>> |
>>
>> |
>>
>> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
>>
>> Regards
>> Sanjiv Singh
>> Mob : +091 9990-447-339
>>
>> On Thu, Jun 23, 2016 at 2:45 AM, Gopal Vijayaraghavan <[email protected]>
>> wrote:
>>
>>>
>>> > Long running query :
>>>
>>> Are you running this on MapReduce or Tez?
>>>
>>> Please post the output of explain - if you are seeing > 1 shuffle edge in
>>> your query while having only one window for OVER(), that might be the
>>> reason.
>>>
>>> OVER ( PARTITION BY m_d_key , sb_gu_key ORDER BY t_ev_st_dt)
>>>
>>>
>>> The multiple PTF operators should have been collapsed by the reduce
>>> sink-deduplication.
>>>
>>> Cheers,
>>> Gopal
>>>
>>>
>>>
>>
>
