Hi,
Here you go:
hive> show create table mine;
OK
CREATE TABLE `mine`(
`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)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES (
'path'='hdfs://localhost:8020/user/hive/warehouse/mine')
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
'file:/Users/ravishankarnair/spark-warehouse/mine'
TBLPROPERTIES (
'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}},{\"name\":\"statecode\",\"type\":\"string\",\"nullable\":true,\"metadata\":{\"name\":\"statecode\",\"scale\":0}},{\"name\":\"Socialid\",\"type\":\"string\",\"nullable\":true,\"metadata\":{\"name\":\"Socialid\",\"scale\":0}},{\"name\":\"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\":true,\"metadata\":{\"name\":\"hu_site_limit\",\"scale\":2}},{\"name\":\"fl_site_limit\",\"type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{\"name\":\"fl_site_limit\",\"scale\":2}},{\"name\":\"fr_site_limit\",\"type\":\"decimal(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_2015\",\"type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{\"name\":\"tiv_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\":true,\"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}},{\"name\":\"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\":{\"name\":\"construction\",\"scale\":0}},{\"name\":\"point_granularity\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{\"name\":\"point_granularity\",\"scale\":0}}]}',
'transient_lastDdlTime'='1518335598')
Time taken: 0.13 seconds, Fetched: 35 row(s)
On Sun, Feb 11, 2018 at 2:36 AM, Shmuel Blitz <shmuel.bl...@similarweb.com>
wrote:
> Please run the following command, and paste the result:
> SHOW CREATE TABLE <<TABLE-NAME>>
>
> On Sun, Feb 11, 2018 at 7:56 AM, ☼ R Nair (रविशंकर नायर) <
> ravishankar.n...@gmail.com> wrote:
>
>> No, No luck.
>>
>> Thanks
>>
>> On Sun, Feb 11, 2018 at 12:48 AM, Deepak Sharma <deepakmc...@gmail.com>
>> wrote:
>>
>>> In hive cli:
>>> msck repair table 《table_name》;
>>>
>>> Thanks
>>> Deepak
>>>
>>> On Feb 11, 2018 11:14, "☼ R Nair (रविशंकर नायर)" <
>>> ravishankar.n...@gmail.com> wrote:
>>>
>>>> NO, can you pease explain the command ? Let me try now.
>>>>
>>>> Best,
>>>>
>>>> On Sun, Feb 11, 2018 at 12:40 AM, Deepak Sharma <deepakmc...@gmail.com>
>>>> wrote:
>>>>
>>>>> I am not sure about the exact issue bjt i see you are partioning while
>>>>> writing from spark.
>>>>> Did you tried msck repair on the table before reading it in hive ?
>>>>>
>>>>> Thanks
>>>>> Deepak
>>>>>
>>>>> On Feb 11, 2018 11:06, "☼ R Nair (रविशंकर नायर)" <
>>>>> ravishankar.n...@gmail.com> wrote:
>>>>>
>>>>>> 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 ="/user/hive/warehouse"
>>>>>>
>>>>>> val spark = SparkSession.builder().appName("Spark Hive
>>>>>> Example").config("spark.sql.warehouse.dir",
>>>>>> warehouseLocation).enableHiveSupport().getOrCreate()
>>>>>>
>>>>>> import org.apache.spark.sql._
>>>>>> var passion_df = 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,\"m
>>>>>> etadata\":{\"name\":\"statecode\",\"scale\":0}},{\"name\":\"
>>>>>> Socialid\",\"type\":\"string\",\"nullable\":true,\"metadata\
>>>>>> ":{\"name\":\"Socialid\",\"scale\":0}},{\"name\":\"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\":true,\"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\":\"decimal(10,2)\",\"nullab
>>>>>> le\":true,\"metadata\":{\"name\":\"fr_site_limit\",\"sca
>>>>>> le\":2}},{\"name\":\"tiv_2014\",\"type\":\"decimal(10,2)\",\
>>>>>> "nullable\":true,\"metadata\":{\"name\":\"tiv_2014\",\"
>>>>>> scale\":2}},{\"name\":\"tiv_2015\",\"type\":\"decimal(10,
>>>>>> 2)\",\"nullable\":true,\"metadata\":{\"name\":\"tiv_
>>>>>> 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\":true,\"metada
>>>>>> ta\":{\"name\":\"hu_site_deductible\",\"scale\":0}},{\"
>>>>>> name\":\"fl_site_deductible\",\"type\":\"integer\",\"nullabl
>>>>>> e\":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}},{\"name\":\"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\":{\"name\":\"
>>>>>> 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.p
>>>>>> arquet.serde.ParquetHiveSerDe
>>>>>> InputFormat: org.apache.hadoop.hive.ql.io.p
>>>>>> arquet.MapredParquetInputFormat
>>>>>> OutputFormat: org.apache.hadoop.hive.ql.io.p
>>>>>> arquet.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.hakob...@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
>>>>>>> know 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.hakob...@rallyhealth.com
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Feb 9, 2018 at 9:59 AM, Prakash Joshi <
>>>>>>> prakashcjos...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Ravi,
>>>>>>>>
>>>>>>>> Can you send the result of
>>>>>>>> Show create table your_table_name
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Prakash
>>>>>>>>
>>>>>>>> On Feb 9, 2018 8:20 PM, "☼ R Nair (रविशंकर नायर)" <
>>>>>>>> ravishankar.n...@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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>>
>>>>
>>>>
>>>> --
>>>>
>>>>
>>
>>
>> --
>>
>>
>
>
> --
> Shmuel Blitz
> Big Data Developer
> Email: shmuel.bl...@similarweb.com
> www.similarweb.com
> <https://www.facebook.com/SimilarWeb/>
> <https://www.linkedin.com/company/429838/>
> <https://twitter.com/similarweb>
>
--
