From user-return-73567-apmail-spark-user-archive=spark.apache.org@spark.apache.org Sun Feb 11 05:36:54 2018 Return-Path: X-Original-To: apmail-spark-user-archive@minotaur.apache.org Delivered-To: apmail-spark-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id A479618BE3 for ; Sun, 11 Feb 2018 05:36:54 +0000 (UTC) Received: (qmail 30900 invoked by uid 500); 11 Feb 2018 05:36:46 -0000 Delivered-To: apmail-spark-user-archive@spark.apache.org Received: (qmail 30782 invoked by uid 500); 11 Feb 2018 05:36:46 -0000 Mailing-List: contact user-help@spark.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Delivered-To: mailing list user@spark.apache.org Received: (qmail 30772 invoked by uid 99); 11 Feb 2018 05:36:46 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 11 Feb 2018 05:36:46 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id DA4D01A09CD for ; Sun, 11 Feb 2018 05:36:45 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.879 X-Spam-Level: ** X-Spam-Status: No, score=2.879 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, MANY_SPAN_IN_TEXT=1, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id JNMKeDzTm6L5 for ; Sun, 11 Feb 2018 05:36:42 +0000 (UTC) Received: from mail-lf0-f49.google.com (mail-lf0-f49.google.com [209.85.215.49]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id D5C215F16A for ; Sun, 11 Feb 2018 05:36:41 +0000 (UTC) Received: by mail-lf0-f49.google.com with SMTP id q194so16348277lfe.13 for ; Sat, 10 Feb 2018 21:36:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=IqltL16ux4fOdH+QFWvdSiczYoLfcx7GU9qSUtzw8fE=; b=glWTwAb/ICTZyV2vFTpNrvyTD5PcLu++rzpY+EJVRlheIN2UXPqq8MwDWRCL0XAipF dQY/vZgZVSKbrc0EofpfL6sR3M7I8ZkxaUh6XAZH4pFRmQ/GmadZHlglncgpxRAK3krC Q+LnFMaHN99RZ0+k0VSivFv07b4c7jYT3VjMVsQv0MVzMxFR4VqnQaNM281oL6RuFBWJ VH8lkyj4OSgR4Cl9n0B6EVQ+uxC3q8kiRI9Ossm8lph9fkxXgt0gor5hRCOYY92actx3 IEFhHZdyW4hOH/BcngguU2Mm6aFXXecASy742Iwxnfp1Aw7SdcRvlidQj85MYUvJMDa/ dBjQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=IqltL16ux4fOdH+QFWvdSiczYoLfcx7GU9qSUtzw8fE=; b=trKQYsqZ01K+z7umxGNqU+/liOMOzsBbpNk4zOPTZVVnqHLbdo6L8HUD1eip5F6Stn +4rXMkuQ5kpz/lSifnqYWb6Dwl4tU3puD+Fn4asThUEFinuLWWwhrLa8qz0Si8xP1mqE XprMi7FCN8yIXKH5F4zS+LM1vPQLQab1lZ/H3I+J4v+QqxAgTSUGTDOhej9+FuPIy4/G Q9Atk3XlM1LY53qs2rXIMQr+KBArhtpc4OT3hT69lVj+Q0J2hayNc40EpjGMqoyoYcl4 WiJny1CcFxHuPabJVkgHa4tSBjRRXyp/OEy1TAB8sTdDFznDCEFBUt4eXqSGmVCfyjn8 scKQ== X-Gm-Message-State: APf1xPBNyN+BIqKzApnOxst1vyXP1abe2N6s66ivhxus+qHPObTlAaaI Y1dpme51qQFVedPh7oj72snQMsNW5UhQYuc3NoU= X-Google-Smtp-Source: AH8x226I0/8bASiBFZoeOyb3pUwU7pCD0gdhcRJVmWH7jlnln7c35miFUFba4DbwIWg6/EQ865gRgjpA8UNKCdOtFLE= X-Received: by 10.25.25.208 with SMTP id 199mr5100053lfz.146.1518327400959; Sat, 10 Feb 2018 21:36:40 -0800 (PST) MIME-Version: 1.0 Received: by 10.25.56.18 with HTTP; Sat, 10 Feb 2018 21:36:40 -0800 (PST) In-Reply-To: References: From: =?UTF-8?B?4pi8IFIgTmFpciAo4KSw4KS14KS/4KS24KSC4KSV4KSwIOCkqOCkvuCkr+CksCk=?= Date: Sun, 11 Feb 2018 00:36:40 -0500 Message-ID: Subject: Re: Spark Dataframe and HIVE To: Nicholas Hakobian Cc: Prakash Joshi , user Content-Type: multipart/alternative; boundary="001a11401f306317bf0564e925bb" --001a11401f306317bf0564e925bb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable All, Thanks for the inputs. Again I am not successful. I think, we need to resolve this, as this is a very common requirement. Please go through my complete code: STEP 1: Started Spark shell as spark-shell --master yarn STEP 2: Flowing code is being given as inout to shark shell import org.apache.spark.sql.Row import org.apache.spark.sql.SparkSession val warehouseLocation =3D"/user/hive/warehouse" val spark =3D SparkSession.builder().appName("Spark Hive Example").config("spark.sql.warehouse.dir", warehouseLocation).enableHiveSupport().getOrCreate() import org.apache.spark.sql._ var passion_df =3D spark.read. format("jdbc"). option("url", "jdbc:mysql://localhost:3307/policies"). option("driver" ,"com.mysql.jdbc.Driver"). option("user", "root"). option("password", "root"). option("dbtable", "insurancedetails"). option("partitionColumn", "policyid"). option("lowerBound", "1"). option("upperBound", "100000"). option("numPartitions", "4"). load() //Made sure that passion_df is created, as passion_df.show(5) shows me correct data. passion_df.write.saveAsTable("default.mine") //Default parquet STEP 3: Went to HIVE. Started HIVE prompt. hive> show tables; OK callcentervoicelogs mine Time taken: 0.035 seconds, Fetched: 2 row(s) //As you can see HIVE is showing the table "mine" in default schema. STEP 4: HERE IS THE PROBLEM. hive> select * from mine; OK Time taken: 0.354 seconds hive> //Where is the data ??? STEP 5: See the below command on HIVE describe formatted mine; OK # col_name data_type comment policyid int statecode string socialid string county string eq_site_limit decimal(10,2) hu_site_limit decimal(10,2) fl_site_limit decimal(10,2) fr_site_limit decimal(10,2) tiv_2014 decimal(10,2) tiv_2015 decimal(10,2) eq_site_deductible int hu_site_deductible int fl_site_deductible int fr_site_deductible int latitude decimal(6,6) longitude decimal(6,6) line string construction string point_granularity int # Detailed Table Information Database: default Owner: ravishankarnair CreateTime: Sun Feb 11 00:26:40 EST 2018 LastAccessTime: UNKNOWN Protect Mode: None Retention: 0 Location: file:/Users/ravishankarnair/spark-warehouse/mine Table Type: MANAGED_TABLE Table Parameters: spark.sql.sources.provider parquet spark.sql.sources.schema.numParts 1 spark.sql.sources.schema.part.0 {\"type\":\"struct\",\"fields\":[{\"name\":\"policyid\",\"type\":\"integer\= ",\"nullable\":true,\"metadata\":{\"name\":\"policyid\",\"scale\":0}},{\"na= me\":\"statecode\",\"type\":\"string\",\"nullable\":true,\"metadata\":{\"na= me\":\"statecode\",\"scale\":0}},{\"name\":\"Socialid\",\"type\":\"string\"= ,\"nullable\":true,\"metadata\":{\"name\":\"Socialid\",\"scale\":0}},{\"nam= e\":\"county\",\"type\":\"string\",\"nullable\":true,\"metadata\":{\"name\"= :\"county\",\"scale\":0}},{\"name\":\"eq_site_limit\",\"type\":\"decimal(10= ,2)\",\"nullable\":true,\"metadata\":{\"name\":\"eq_site_limit\",\"scale\":= 2}},{\"name\":\"hu_site_limit\",\"type\":\"decimal(10,2)\",\"nullable\":tru= e,\"metadata\":{\"name\":\"hu_site_limit\",\"scale\":2}},{\"name\":\"fl_sit= e_limit\",\"type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{\"name= \":\"fl_site_limit\",\"scale\":2}},{\"name\":\"fr_site_limit\",\"type\":\"d= ecimal(10,2)\",\"nullable\":true,\"metadata\":{\"name\":\"fr_site_limit\",\= "scale\":2}},{\"name\":\"tiv_2014\",\"type\":\"decimal(10,2)\",\"nullable\"= :true,\"metadata\":{\"name\":\"tiv_2014\",\"scale\":2}},{\"name\":\"tiv_201= 5\",\"type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{\"name\":\"t= iv_2015\",\"scale\":2}},{\"name\":\"eq_site_deductible\",\"type\":\"integer= \",\"nullable\":true,\"metadata\":{\"name\":\"eq_site_deductible\",\"scale\= ":0}},{\"name\":\"hu_site_deductible\",\"type\":\"integer\",\"nullable\":tr= ue,\"metadata\":{\"name\":\"hu_site_deductible\",\"scale\":0}},{\"name\":\"= fl_site_deductible\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{\= "name\":\"fl_site_deductible\",\"scale\":0}},{\"name\":\"fr_site_deductible= \",\"type\":\"integer\",\"nullable\":true,\"metadata\":{\"name\":\"fr_site_= deductible\",\"scale\":0}},{\"name\":\"latitude\",\"type\":\"decimal(6,6)\"= ,\"nullable\":true,\"metadata\":{\"name\":\"latitude\",\"scale\":6}},{\"nam= e\":\"longitude\",\"type\":\"decimal(6,6)\",\"nullable\":true,\"metadata\":= {\"name\":\"longitude\",\"scale\":6}},{\"name\":\"line\",\"type\":\"string\= ",\"nullable\":true,\"metadata\":{\"name\":\"line\",\"scale\":0}},{\"name\"= :\"construction\",\"type\":\"string\",\"nullable\":true,\"metadata\":{\"nam= e\":\"construction\",\"scale\":0}},{\"name\":\"point_granularity\",\"type\"= :\"integer\",\"nullable\":true,\"metadata\":{\"name\":\"point_granularity\"= ,\"scale\":0}}]} transient_lastDdlTime 1518326800 # Storage Information SerDe Library: org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe InputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat Compressed: No Num Buckets: -1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: path hdfs://localhost:8020/user/hive/warehouse/mine serialization.format 1 Time taken: 0.077 seconds, Fetched: 48 row(s) Now, I see your advise and support. Whats the issue? Am I doing wrong, it it a bug ? I am using Spark 2.2.1, HIVE 1.2.1, HADOOP 2.7.3. All class path, configuration are set properly. Best, Ravion On Fri, Feb 9, 2018 at 1:29 PM, Nicholas Hakobian < nicholas.hakobian@rallyhealth.com> wrote: > Its possible that the format of your table is not compatible with your > version of hive, so Spark saved it in a way such that only Spark can read > it. When this happens it prints out a very visible warning letting you kn= ow > this has happened. > > We've seen it most frequently when trying to save a parquet file with a > column in date format into a Hive table. In older versions of hive, its > parquet reader/writer did not support Date formats (among a couple others= ). > > Nicholas Szandor Hakobian, Ph.D. > Staff Data Scientist > Rally Health > nicholas.hakobian@rallyhealth.com > > > On Fri, Feb 9, 2018 at 9:59 AM, Prakash Joshi > wrote: > >> Ravi, >> >> Can you send the result of >> Show create table your_table_name >> >> Thanks >> Prakash >> >> On Feb 9, 2018 8:20 PM, "=E2=98=BC R Nair (=E0=A4=B0=E0=A4=B5=E0=A4=BF= =E0=A4=B6=E0=A4=82=E0=A4=95=E0=A4=B0 =E0=A4=A8=E0=A4=BE=E0=A4=AF=E0=A4=B0)"= < >> ravishankar.nair@gmail.com> wrote: >> >>> All, >>> >>> It has been three days continuously I am on this issue. Not getting any >>> clue. >>> >>> Environment: Spark 2.2.x, all configurations are correct. hive-site.xml >>> is in spark's conf. >>> >>> 1) Step 1: I created a data frame DF1 reading a csv file. >>> >>> 2) Did manipulations on DF1. Resulting frame is passion_df. >>> >>> 3) passion_df.write.format("orc").saveAsTable("sampledb.passion") >>> >>> 4) The metastore shows the hive table., when I do "show tables" in HIVE= , >>> I can see table name >>> >>> 5) I can't select in HIVE, though I can select from SPARK as >>> spark.sql("select * from sampledb.passion") >>> >>> Whats going on here? Please help. Why I am not seeing data from HIVE >>> prompt? >>> The "describe formatted " command on the table in HIVE shows he data is >>> is in default warehouse location ( /user/hive/warehouse) since I set it= . >>> >>> I am not getting any definite answer anywhere. Many suggestions and >>> answers given in Stackoverflow et al.Nothing really works. >>> >>> So asking experts here for some light on this, thanks >>> >>> Best, >>> Ravion >>> >>> >>> > -- --001a11401f306317bf0564e925bb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
All,

Thanks for the inputs. Again I am = not successful. I think, we need to resolve this, as this is a very common = requirement.

Please go through my complete code:

STEP 1: =C2=A0Started Spark shell as spark-shell --= master yarn

STEP 2: Flowing code is being given as= inout to shark shell

import org.apache.spark= .sql.Row
import org.apache.spark.sql.SparkSession
val w= arehouseLocation =3D"/user/hive/warehouse"

val spark =3D SparkSession.builder().appName("Spark Hive Example&qu= ot;).config("spark.sql.warehouse.dir", warehouseLocation).enableH= iveSupport().getOrCreate()

import org.apache.spark= .sql._
var passion_df =3D spark.read.
format("jdbc= ").
option("url", "jdbc:mysql://localhost:330= 7/policies").
option("driver" ,"com.mysql.jdb= c.Driver").
option("user", "root").
option("password", "root").
option(&quo= t;dbtable", "insurancedetails").
option("part= itionColumn", "policyid").
option("lowerBound= ", "1").
option("upperBound", "1000= 00").
option("numPartitions", "4").
load()
//Made sure that passion_df is created, as passion_d= f.show(5) shows me correct data.
passion_df.write.saveAsTable(&qu= ot;default.mine") //Default parquet

STE= P 3: Went to HIVE. Started HIVE prompt.=C2=A0

hive> show tables;
OK
callcentervoicelogs
mine =C2=A0 =C2=A0 =C2=A0
Time taken: 0.035 seconds, Fetched: 2 = row(s)
//As you can see HIVE is showing the table "min= e" in default schema.

STEP 4: HERE IS THE= PROBLEM.=C2=A0

hive> select * from mine;<= /div>
OK
Time taken: 0.354 seconds
hive>=C2=A0
//Where is the data ???

STEP 5:

See the below command on HIVE

describe formatted mine;
OK
# col_name =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 data_type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = comm= ent =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=C2=A0
p= olicyid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0
statecode =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 string =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
socialid =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 string =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0
county =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 string = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
eq_site_limit =C2=A0 = =C2=A0 =C2=A0 decimal(10,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
hu_site_limit =C2=A0 =C2= =A0 =C2=A0 = decimal(10,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
fl_site_limit =C2=A0 =C2=A0 = =C2=A0 decimal(10,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
fr_site_limit =C2=A0 =C2=A0 =C2= =A0 = decimal(10,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0
tiv_2014 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 decimal(10,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
tiv_2015 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 decimal(10,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
eq_site_deductible = =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
hu_site_d= eductible =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
f= l_site_deductible =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
fr_site_deductible =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0
latitude =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 decimal(6,6)= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0
longitude =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= dec= imal(6,6) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0
line =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 string =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
construction =C2=A0 =C2=A0 =C2=A0 =C2=A0 string =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0
point_granularity =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0
# Detailed Table Information<= span class=3D"gmail-Apple-tab-span" style=3D"white-space:pre"> =C2=A0
Database: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 default =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Owner: =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ravishankarnair =C2=A0 =C2=A0 =C2=A0
CreateTime: =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Sun Feb 11 00:26:40 EST 2018 =C2=A0
LastAccessTim= e: =C2=A0 =C2=A0 UNKNOWN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Protect Mode: =C2=A0 =C2=A0 =C2=A0 None =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0
Retention: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Location: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 file:/Users/ravishankarnair/spar= k-warehouse/mine =C2=A0
Table Type: =C2=A0 =C2=A0 =C2=A0 =C2=A0 MANAGED_TA= BLE =C2=A0 =C2=A0 =C2=A0 =C2=A0
Table Parameters: =C2=A0
spark.sql.sour= ces.provider= parquet =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
<= span class=3D"gmail-Apple-tab-span" style=3D"white-space:pre"> spark= .sql.sources.schema.numParts 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0=C2=A0
spark.sql.sources.schema.part.0 {\"type\":\&= quot;struct\",\"fields\":[{\"name\":\"policyi= d\",\"type\":\"integer\",\"nullable\":tr= ue,\"metadata\":{\"name\":\"policyid\",\"= ;scale\":0}},{\"name\":\"statecode\",\"type\&= quot;:\"string\",\"nullable\":true,\"metadata\&quo= t;:{\"name\":\"statecode\",\"scale\":0}},{\&q= uot;name\":\"Socialid\",\"type\":\"string\&qu= ot;,\"nullable\":true,\"metadata\":{\"name\":= \"Socialid\",\"scale\":0}},{\"name\":\"c= ounty\",\"type\":\"string\",\"nullable\"= :true,\"metadata\":{\"name\":\"county\",\&quo= t;scale\":0}},{\"name\":\"eq_site_limit\",\"t= ype\":\"decimal(10,2)\",\"nullable\":true,\"m= etadata\":{\"name\":\"eq_site_limit\",\"scale= \":2}},{\"name\":\"hu_site_limit\",\"type\&qu= ot;:\"decimal(10,2)\",\"nullable\":true,\"metadata= \":{\"name\":\"hu_site_limit\",\"scale\"= :2}},{\"name\":\"fl_site_limit\",\"type\":\&q= uot;decimal(10,2)\",\"nullable\":true,\"metadata\"= :{\"name\":\"fl_site_limit\",\"scale\":2}},{\= "name\":\"fr_site_limit\",\"type\":\"dec= imal(10,2)\",\"nullable\":true,\"metadata\":{\&quo= t;name\":\"fr_site_limit\",\"scale\":2}},{\"n= ame\":\"tiv_2014\",\"type\":\"decimal(10,2)\&= quot;,\"nullable\":true,\"metadata\":{\"name\"= ;:\"tiv_2014\",\"scale\":2}},{\"name\":\"= ;tiv_2015\",\"type\":\"decimal(10,2)\",\"null= able\":true,\"metadata\":{\"name\":\"tiv_2015= \",\"scale\":2}},{\"name\":\"eq_site_deductib= le\",\"type\":\"integer\",\"nullable\":t= rue,\"metadata\":{\"name\":\"eq_site_deductible\&q= uot;,\"scale\":0}},{\"name\":\"hu_site_deductible\= ",\"type\":\"integer\",\"nullable\":true= ,\"metadata\":{\"name\":\"hu_site_deductible\"= ;,\"scale\":0}},{\"name\":\"fl_site_deductible\&qu= ot;,\"type\":\"integer\",\"nullable\":true,\&= quot;metadata\":{\"name\":\"fl_site_deductible\",\= "scale\":0}},{\"name\":\"fr_site_deductible\"= ,\"type\":\"integer\",\"nullable\":true,\&quo= t;metadata\":{\"name\":\"fr_site_deductible\",\&qu= ot;scale\":0}},{\"name\":\"latitude\",\"type\= ":\"decimal(6,6)\",\"nullable\":true,\"metada= ta\":{\"name\":\"latitude\",\"scale\":6}= },{\"name\":\"longitude\",\"type\":\"dec= imal(6,6)\",\"nullable\":true,\"metadata\":{\"= ;name\":\"longitude\",\"scale\":6}},{\"name\&= quot;:\"line\",\"type\":\"string\",\"nul= lable\":true,\"metadata\":{\"name\":\"line\&q= uot;,\"scale\":0}},{\"name\":\"construction\"= ,\"type\":\"string\",\"nullable\":true,\"= ;metadata\":{\"name\":\"construction\",\"scal= e\":0}},{\"name\":\"point_granularity\",\"typ= e\":\"integer\",\"nullable\":true,\"metadata\= ":{\"name\":\"point_granularity\",\"scale\&qu= ot;:0}}]}
transient_lastDdlTime 1518326800 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0
= =C2=A0
# Storage Information =C2=A0
SerDe Library: =C2= =A0 =C2=A0 =C2=A0 org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe =C2=A0<= /div>
InputFormat: =C2=A0 =C2=A0 =C2=A0 =C2=A0 org.apache.hadoop.hive.ql.io.= parquet.MapredParquetInputFormat =C2=A0
OutputFormat: =C2=A0 =C2=A0 = =C2=A0 org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat =C2=A0
Compressed: =C2=A0 =C2=A0 =C2=A0 =C2=A0 No =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Num Buckets: =C2=A0 =C2=A0 = =C2=A0 =C2=A0 -1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<= span class=3D"gmail-Apple-tab-span" style=3D"white-space:pre"> =C2= =A0
Bucket Columns: =C2=A0 =C2=A0 [] =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Sort Columns: =C2=A0 =C2=A0 = =C2=A0 [] =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Storage Desc Params: =C2=A0
path =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 hdfs://localhost:8020/user/hive/warehouse/mine
serializa= tion.format = 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2= =A0
Time taken: 0.077 seconds, Fetched: 48 row(s)

Now, I see your advise and support. Whats the issue? Am I d= oing wrong, it it a bug ? I am using Spark 2.2.1, HIVE 1.2.1, HADOOP 2.7.3.= All class path, configuration are set properly.

B= est,

Ravion
=
On Fri, Feb 9, 2018 at 1:29 PM, Nicholas Hak= obian <nicholas.hakobian@rallyhealth.com> wr= ote:
Its possible that t= he format of your table is not compatible with your version of hive, so Spa= rk saved it in a way such that only Spark can read it. When this happens it= prints out a very visible warning letting you know this has happened.
=
We've seen it most frequently when trying to save a parq= uet file with a column in date format into a Hive table. In older versions = of hive, its parquet reader/writer did not support Date formats (among a co= uple others).

Nicholas Szandor Hakobian, Ph.D.
Sta= ff Data Scientist
Rally Health


On Fri, Feb 9, 2018 at 9:59 AM, Prakash Josh= i <prakashcjoshi5@gmail.com> wrote:
Ravi,

Can you send the result of=C2=A0
Show cr= eate table your_table_name

Thanks=C2=A0
Prakash
=

On Feb 9, 2018 8:= 20 PM, "=E2=98=BC R Nair (=E0=A4=B0=E0=A4=B5=E0=A4=BF=E0=A4=B6=E0=A4= =82=E0=A4=95=E0=A4=B0 =E0=A4=A8=E0=A4=BE=E0=A4=AF=E0=A4=B0)" <ravishankar.nair= @gmail.com> wrote:
All,

It has been three days conti= nuously I am on this issue. Not getting any clue.=C2=A0

Environment: Spark 2.2.x, all configurations are correct. hive-site.x= ml is in spark's conf.

1) Step 1: I created a = data frame DF1 reading a csv file.

2) Did =C2=A0ma= nipulations on DF1. Resulting frame is passion_df.

3) passion_df.write.format("orc").saveAsTable("sampled= b.passion")

4) The metastore shows the hive ta= ble., when I do "show tables" in HIVE, I can see table name
=

5) I can't select in HIVE, though I can select from= SPARK as spark.sql("select * from sampledb.passion")
<= br>
Whats going on here? Please help. Why I am not seeing data fr= om HIVE prompt?
The "describe formatted " command on th= e table in HIVE shows he data is is in default warehouse location ( /user/h= ive/warehouse) since I set it.

I am not getting an= y definite answer anywhere. Many suggestions and answers given in Stackover= flow et al.Nothing really works.

So asking experts= here for some light on this, thanks

Best,
Ravion=C2=A0






--

=
--001a11401f306317bf0564e925bb--