Big Data

Get Involved. Join the Conversation.

Topic

    candan pehlivanoglu
    BDA smart scan problem
    Topic posted December 6, 2018 by candan pehlivanogluGreen Ribbon: 100+ Points, tagged Big Data Appliance, Big Data SQL 
    198 Views, 6 Comments
    Title:
    BDA smart scan problem
    Summary:
    For one table offload is increasing
    Content:

    Hello all;

    We have a trouble about big data sql . Normally, our smart scan is working properly. But, for one table I saw predicate offload retries value is increasing. I don't know what I should check for one table. Could you help me?

    Version:
    3.1

    Comment

     

    • Marty Gubar

      Hi Candan -

      Can you provide some more details about the error?  Are you saying that some queries offload without retries - while other queries do see retries?  If that's the case, can you share the ddl for those tables that are showing retries?

      Thanks.
      Marty

      • candan pehlivanoglu

        Sorry Marty for delayed answer.

        Yes you are right. Some queries were working properly but for some tables it didn't work. I had requested a code development from our team. The below parameter fixed the problem

        --conf "spark.sql.parquet.writeLegacyFormat=true" fixed the problem.

         

         

          

         

    • Marty Gubar

      Nice job sorting this out Candan :)

      I'm curious - would you mind posting the Hive DDL and the corresponding Oracle DDL?  This could be helpful to others.

      Thanks!

      Marty

    • candan pehlivanoglu

       Ok I attached

      hive_table.sql (797 Bytes)
    • Marty Gubar

      Hi Candan -

      This looks like a hive table over a CSV table.  Any chance you have the ddl for the Parquet-based table?

      Thanks!

      Marty

    • candan pehlivanoglu

      Sorry, We were at holiday. I didn't see this comment. It is my fault. I uploaded.

       

      CREATE EXTERNAL TABLE `firmarec`(
        `snpst_dt` timestamp,
        `src_dt` timestamp,
        `upd_dt` timestamp,
        `hex_payload` string,
        `frsbkd` string,
        `frtelno` string,
        `fr_konharf` string,
        `fr_durumkodu` string,
        `fr_yetkikodu` string,
        `fr_adi1` string,
        `fr_adi2` string,
        `fr_adres1` string,
        `fr_adres2` string,
        `fr_telefon_1` bigint,
        `fr_telefon_2` bigint,
        `fr_dogum_tarihi_yyag` bigint,
        `fr_dogum_yeri` string,
        `fr_baba_adi` string,
        `fr_kisilikkd` string,
        `fr_kisiliknev` int,
        `fr_merkilkod` int,
        `fr_yer_kodu` string,
        `fr_kit_kodu` string,
        `fr_nitelik_kodu` string,
        `fr_tescil_durumu` string,
        `fr_istirak_kodu` string,
        `fr_istigal_harfi` string,
        `fr_musteri_no` bigint,
        `fr_vergi_dai_kod` string,
        `fr_vergi_dai_sicilno` string,
        `fr_vkn` string,
        `fr_ulk_kodu` string,
        `fr_memzuc_kodu` string,
        `fr_sicilno` string,
        `fr_kib_sicilno` string,
        `fr_tic_sic_memkd` string,
        `fr_kib_sic_turu` string,
        `fr_tic_sic_ilcekd` string,
        `fr_kib_sehir_kodu` string,
        `fr_kredi_grubu` string,
        `fr_provizyon_kodu` string,
        `fr_prov_veren_fir_anah` bigint,
        `fr_prov_veren_fir_anah_eski` bigint,
        `fr_prov_iptal_yyag` bigint,
        `fr_birlesen_fir_anah` bigint,
        `fr_acilis_tarihi_yyag` bigint,
        `fr_son_kullandirma_yyag` bigint,
        `fr_son_update_tarihi_yyag` bigint,
        `fr_onc_son_kullandirma_yyag` bigint,
        `fr_istihbarat_tarihi_yyag` bigint,
        `fr_ilk_tahsis_tarihi_yyag` bigint,
        `fr_risk_yyyyaa` bigint,
        `fm_isl_yyyyaa` bigint,
        `fr_memzuc_geri_bildirim_yyyyaa` bigint,
        `fr_bankamiz_yyyyaa` bigint,
        `fr_memzuc_bankamiz_yyyyaa` bigint,
        `fr_tasfiye_tarihi_yyag` bigint,
        `fr_tasfiye_nedeni` string,
        `fr_yeniden_yapilandirma` string,
        `fr_firma_turu` string,
        `fr_yil_istisna` string,
        `fr_ay_istisna` string,
        `fr_cek_ith_ihr_kodu` string,
        `fr_kategori` string,
        `fr_yeni_kisilik_nevi` string,
        `fr_eski_yetkikodu` string,
        `fr_yeni_nitelik_kodu` int,
        `fr_sakla_yetkikodu` string,
        `fr_devir_nedeni` string,
        `fr_gv_kod` string,
        `fr_gv_hata_kod` string,
        `fr_kanuni_takip_varmi` string,
        `fr_kredi_kullaniyormu` string,
        `fr_sirano` bigint,
        `fr_son_teminat_no` int,
        `fr_arsivtar` bigint,
        `fr_bch_idaritakip` string,
        `fr_bch_kanunitakip` string,
        `fr_dbmussnc_flag` string,
        `fr_devir_flag` string,
        `fr_f2_ypdos_flag` string,
        `fr_yhkskd` string,
        `fr_yp_eslemekd` string,
        `fr_tuketici_kodu` string,
        `fr_kredi_teklif_sayisi` int,
        `fr_yetki_flag` string,
        `fr_nm_tasfiye_limit_yp` decimal(20,2),
        `fr_nm_tasfiye_risk_yp` decimal(20,2),
        `fr_aysonu_toplam_limit` decimal(20,2),
        `fr_convert_flag` string,
        `fr_aysonu_firma_turu` string,
        `fr_nm_rotatif_limit_tp` decimal(20,2),
        `fr_nm_rotatif_risk_tp` decimal(20,2),
        `fr_nm_rotatif_limit_yp` decimal(20,2),
        `fr_nm_rotatif_risk_yp` decimal(20,2),
        `fr_nm_tasfiye_limit_tp` decimal(20,2),
        `fr_nm_tasfiye_risk_tp` decimal(20,2),
        `fr_rotatif_limit_tp` decimal(20,2),
        `fr_rotatif_limit_tp_yyag` bigint,
        `fr_rotatif_risk_tp` decimal(20,2),
        `fr_rotatif_limit_yp` decimal(20,2),
        `fr_rotatif_limit_yp_yyag` bigint,
        `fr_rotatif_risk_yp` decimal(20,2),
        `fr_tasfiye_limit_tp` decimal(20,2),
        `fr_tasfiye_limit_tp_yyag` bigint,
        `fr_tasfiye_risk_tp` decimal(20,2),
        `fr_tasfiye_limit_yp` decimal(20,2),
        `fr_tasfiye_limit_yp_yyag` bigint,
        `fr_tasfiye_risk_yp` decimal(20,2),
        `fr_risk_tavani_usd` decimal(20,2),
        `fr_risk_tavani_usd_yyag` bigint,
        `fr_nakdi_risk_yp` decimal(20,2),
        `fr_nakdi_risk_tp` decimal(20,2),
        `fr_bch_risk` decimal(20,2),
        `fr_iskonto_istira_risk` decimal(20,2),
        `fr_sirket_kredi_karti_risk` decimal(20,2),
        `fr_tuketici_risk` decimal(20,2),
        `fr_ticari_ek_hesap_risk` decimal(20,2),
        `fr_gayrinakdi_risk_yp` decimal(20,2),
        `fr_gayrinakdi_risk_tp` decimal(20,2),
        `fr_tazmin_tp` decimal(20,2),
        `fr_tazmin_yp` decimal(20,2),
        `fr_ilk_kr_tahsistar_yyag` bigint,
        `fr_vkn_tckn` bigint,
        `fr_tckn` bigint,
        `fr_gecen_aysonu_rotlim_tl` decimal(20,2),
        `fr_gecen_aysonu_rotlim_yp` bigint,
        `fr_gecen_aysonu_taslim_tl` decimal(20,2),
        `fr_gecen_aysonu_taslim_yp` bigint,
        `fr_upd_check_str` string,
        `fr_upd_date_time` bigint,
        `fr_risk_tavani_dvzkd` string,
        `fr_rating_sektor_kodu` string,
        `fr_gecen_aysonu_top_nak_tl` decimal(20,2),
        `fr_gecen_aysonu_top_nak_yp` bigint,
        `fr_gecen_aysonu_top_gnak_tl` decimal(20,2),
        `fr_gecen_aysonu_top_gnak_yp` bigint,
        `fr_vob_risk_tp` decimal(20,2),
        `fr_kyp_son_onay_tarihi_yyag` bigint,
        `fr_kyp_son_onay_saati_hhmmssff` bigint,
        `fr_prop_validity_start_yyag` bigint,
        `fr_prop_validity_end_yyag` bigint,
        `fr_active_proposal_flag` string,
        `fr_active_switching_flag` string,
        `fr_active_provizyon_flag` string,
        `fr_customer_transfer_flag` string,
        `fr_son_teklif_onay_birimi` string,
        `fr_kyp_son_onay_merci_kodu` string,
        `fr_kyp_son_onayli_proposal_id` string,
        `fr_kyp_pilot_flag` string,
        `fr_santral_devri_flag` string,
        `fr_tic_krd_cnt` int,
        `fr_bir_krd_cnt` int,
        `fr_tasima_kodu` string,
        `fr_nace_istigal` string,
        `fr_nace_kisim` string,
        `fr_nace_bolum` string,
        `fr_nace_grup1` string,
        `fr_nace_grup2` string,
        `fr_nace_grup3` string,
        `fr_nace_grup4` string,
        `fr_birlesen_yyag` bigint,
        `fr_ciro_gecen_yil` bigint,
        `fr_ciro_2yil_once` bigint,
        `fr_calisan_sayisi` bigint,
        `fr_aktiflik_gecen_yil` bigint,
        `fr_aktiflik_2yil_once` bigint,
        `fr_ciro_ismas_ilkgunc_yyag` bigint,
        `fr_ciro_ismas_gecyil_flag` string,
        `fr_ciro_ismas_2yilon_flag` string,
        `fr_vkn_tckn_kod` string,
        `fr_musno_degis_flag` string,
        `fr_rttf_convert` string,
        `fr_reserve_limit_tp` decimal(20,2),
        `fr_reserve_limit_yp` decimal(20,2),
        `fr_reserve_risk_tp` decimal(20,2),
        `fr_reserve_risk_yp` decimal(20,2),
        `fr_filler` string,
        `segckey` string)
      PARTITIONED BY (
        `y` int,
        `m` int,
        `d` int)
      ROW FORMAT SERDE
        'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
      STORED AS INPUTFORMAT
        'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
      OUTPUTFORMAT
        'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
      LOCATION
        'hdfs://bda-ns/data/hive/warehouse/etloffloading.db/firmarec'
      TBLPROPERTIES (
        'transient_lastDdlTime'='1545053179')
      Time taken: 0.257 seconds, Fetched: 194 row(s)
      hive>