ok. I work for an org where such upgrades take a few months. Not an immediate task.
Thanks, Venkat 2016173438 On Fri, Mar 11, 2022 at 4:38 PM Mich Talebzadeh <mich.talebza...@gmail.com> wrote: > yes in spark 3.1.1. Best to upgrade it to spark 3+. > > > > view my Linkedin profile > <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> > > > https://en.everybodywiki.com/Mich_Talebzadeh > > > > *Disclaimer:* Use it at your own risk. Any and all responsibility for any > loss, damage or destruction of data or any other property which may arise > from relying on this email's technical content is explicitly disclaimed. > The author will in no case be liable for any monetary damages arising from > such loss, damage or destruction. > > > > > On Fri, 11 Mar 2022 at 21:35, Venkatesan Muniappan < > m.venkatbe...@gmail.com> wrote: > >> Thank you. I am trying to get the table definition for the existing >> tables. BTW, the create and show command that you executed, was it on Spark >> 3.x ? . >> >> Thanks, >> Venkat >> 2016173438 >> >> >> On Fri, Mar 11, 2022 at 4:28 PM Mich Talebzadeh < >> mich.talebza...@gmail.com> wrote: >> >>> Well I do not know what has changed. However, this should not affect >>> your work. >>> >>> >>> Try to create table in Spark >>> >>> >>> sqltext: String = >>> >>> CREATE TABLE if not exists test.etcs( >>> >>> ID INT >>> >>> , CLUSTERED INT >>> >>> , SCATTERED INT >>> >>> , RANDOMISED INT >>> >>> , RANDOM_STRING VARCHAR(50) >>> >>> , SMALL_VC VARCHAR(10) >>> >>> , PADDING VARCHAR(4000) >>> >>> , PADDING2 STRING >>> >>> ) >>> >>> CLUSTERED BY (ID) INTO 256 BUCKETS >>> >>> STORED AS PARQUET >>> >>> TBLPROPERTIES ( >>> >>> "parquet.compress"="SNAPPY" >>> >>> ) >>> >>> >>> scala> spark.sql (sqltext) >>> >>> scala> spark.sql("show create table test.etcs").show(false) >>> >>> >>> +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>> >>> |createtab_stmt >>> >>> >>> >>> | >>> >>> >>> +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>> >>> |CREATE TABLE `test`.`etcs` ( >>> >>> `ID` INT, >>> >>> `CLUSTERED` INT, >>> >>> `SCATTERED` INT, >>> >>> `RANDOMISED` INT, >>> >>> `RANDOM_STRING` VARCHAR(50), >>> >>> `SMALL_VC` VARCHAR(10), >>> >>> `PADDING` VARCHAR(4000), >>> >>> `PADDING2` STRING) >>> >>> USING parquet >>> >>> CLUSTERED BY (ID) >>> >>> INTO 256 BUCKETS >>> >>> TBLPROPERTIES ( >>> >>> 'transient_lastDdlTime' = '1647033659', >>> >>> 'parquet.compress' = 'SNAPPY') >>> >>> | >>> >>> >>> +------------------------------------------------------------------------------------------------------------------ >>> >>> >>> Note that columns are OK. >>> >>> >>> Also check this link for the differences between CHAR, VARCHAR and >>> STRING types in Hive >>> >>> >>> https://cwiki.apache.org/confluence/display/hive/languagemanual+types >>> >>> >>> HTH >>> >>> >>> >>> view my Linkedin profile >>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> >>> >>> >>> https://en.everybodywiki.com/Mich_Talebzadeh >>> >>> >>> >>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>> any loss, damage or destruction of data or any other property which may >>> arise from relying on this email's technical content is explicitly >>> disclaimed. The author will in no case be liable for any monetary damages >>> arising from such loss, damage or destruction. >>> >>> >>> >>> >>> On Fri, 11 Mar 2022 at 20:55, Venkatesan Muniappan < >>> m.venkatbe...@gmail.com> wrote: >>> >>>> Thank you Mich Talebzadeh for your answer. It's good to know that >>>> VARCHAR and CHAR are properly showing in Spark 3. Do you know what changed >>>> in Spark 3 that made this possible?. Or how can I achieve the same output >>>> in Spark 2.4.1? If there are some conf options, that would be helpful. >>>> >>>> Thanks, >>>> Venkat >>>> 2016173438 >>>> >>>> >>>> On Fri, Mar 11, 2022 at 2:06 PM Mich Talebzadeh < >>>> mich.talebza...@gmail.com> wrote: >>>> >>>>> Hive 3.1.1 >>>>> Spark 3.1.1 >>>>> >>>>> Your stack overflow issue raised and I quote: >>>>> >>>>> "I have a need to generate DDL statements for Hive tables & views >>>>> programmatically. I tried using Spark and Beeline for this task. Beeline >>>>> takes around 5-10 seconds for each of the statements whereas Spark >>>>> completes the same thing in a few milliseconds. I am planning to use Spark >>>>> since it is faster compared to beeline. One downside of using spark for >>>>> getting DDL statements from the hive is, it treats CHAR, VARCHAR >>>>> characters >>>>> as String and it doesn't preserve the length information that goes with >>>>> CHAR,VARCHAR data types. At the same time beeline preserves the data type >>>>> and the length information for CHAR,VARCHAR data types. *I am using >>>>> Spark 2.4.1 and Beeline 2.1.1.* >>>>> >>>>> Given below the sample create table command and its show create table >>>>> output." >>>>> >>>>> Create a simple table in *Hive* in test database >>>>> >>>>> hive> *use test;* >>>>> OK >>>>> hive> *create table etc(ID BIGINT, col1 VARCHAR(30), col2 STRING);* >>>>> OK >>>>> hive> *desc formatted etc;* >>>>> # col_name data_type comment >>>>> *id bigint* >>>>> *col1 varchar(30)* >>>>> *col2 string* >>>>> >>>>> # Detailed Table Information >>>>> Database: test >>>>> OwnerType: USER >>>>> Owner: hduser >>>>> CreateTime: Fri Mar 11 18:29:34 GMT 2022 >>>>> LastAccessTime: UNKNOWN >>>>> Retention: 0 >>>>> Location: >>>>> hdfs://rhes75:9000/user/hive/warehouse/test.db/etc >>>>> Table Type: MANAGED_TABLE >>>>> Table Parameters: >>>>> COLUMN_STATS_ACCURATE >>>>> >>>>> {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"col1\":\"true\",\"col2\":\"true\",\"id\":\"true\"}} >>>>> bucketing_version 2 >>>>> numFiles 0 >>>>> numRows 0 >>>>> rawDataSize 0 >>>>> totalSize 0 >>>>> transient_lastDdlTime 1647023374 >>>>> >>>>> # Storage Information >>>>> SerDe Library: >>>>> org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe >>>>> InputFormat: org.apache.hadoop.mapred.TextInputFormat >>>>> OutputFormat: >>>>> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat >>>>> Compressed: No >>>>> Num Buckets: -1 >>>>> Bucket Columns: [] >>>>> Sort Columns: [] >>>>> Storage Desc Params: >>>>> serialization.format 1 >>>>> >>>>> Now let's go to spark-shell >>>>> ^ >>>>> scala> *spark.sql("show create table test.etc").show(false)* >>>>> >>>>> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> |createtab_stmt >>>>> >>>>> | >>>>> >>>>> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> |CREATE TABLE `test`.`etc` ( >>>>> * `id` BIGINT,* >>>>> * `col1` VARCHAR(30),* >>>>> * `col2` STRING)* >>>>> USING text >>>>> TBLPROPERTIES ( >>>>> 'bucketing_version' = '2', >>>>> 'transient_lastDdlTime' = '1647023374') >>>>> | >>>>> >>>>> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> >>>>> You can see Spark shows columns correctly >>>>> >>>>> Now let us go and create the same table in hive through beeline >>>>> >>>>> >>>>> 0: jdbc:hive2://rhes75:10099/default>* use test* >>>>> >>>>> No rows affected (0.019 seconds) >>>>> >>>>> 0: jdbc:hive2://rhes75:10099/default> *create table etc(ID BIGINT, >>>>> col1 VARCHAR(30), col2 STRING)* >>>>> >>>>> . . . . . . . . . . . . . . . . . . > No rows affected (0.304 seconds) >>>>> >>>>> 0: jdbc:hive2://rhes75:10099/default> *desc formatted etc* >>>>> >>>>> . . . . . . . . . . . . . . . . . . > >>>>> +-------------------------------+----------------------------------------------------+----------------------------------------------------+ >>>>> >>>>> | col_name | data_type >>>>> | comment | >>>>> >>>>> >>>>> +-------------------------------+----------------------------------------------------+----------------------------------------------------+ >>>>> >>>>> | # col_name | data_type >>>>> | comment | >>>>> >>>>> *| id | bigint >>>>> | |* >>>>> >>>>> *| col1 | varchar(30) >>>>> | |* >>>>> >>>>> *| col2 | string >>>>> | |* >>>>> >>>>> | | NULL >>>>> | NULL | >>>>> >>>>> | # Detailed Table Information | NULL >>>>> | NULL | >>>>> >>>>> | Database: | test >>>>> | NULL | >>>>> >>>>> | OwnerType: | USER >>>>> | NULL | >>>>> >>>>> | Owner: | hduser >>>>> | NULL | >>>>> >>>>> | CreateTime: | Fri Mar 11 18:51:00 GMT 2022 >>>>> | NULL | >>>>> >>>>> | LastAccessTime: | UNKNOWN >>>>> | NULL | >>>>> >>>>> | Retention: | 0 >>>>> | NULL | >>>>> >>>>> | Location: | >>>>> hdfs://rhes75:9000/user/hive/warehouse/test.db/etc | NULL >>>>> | >>>>> >>>>> | Table Type: | MANAGED_TABLE >>>>> | NULL | >>>>> >>>>> | Table Parameters: | NULL >>>>> | NULL | >>>>> >>>>> | | COLUMN_STATS_ACCURATE >>>>> | >>>>> {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"col1\":\"true\",\"col2\":\"true\",\"id\":\"true\"}} >>>>> | >>>>> >>>>> | | bucketing_version >>>>> | 2 | >>>>> >>>>> | | numFiles >>>>> | 0 | >>>>> >>>>> | | numRows >>>>> | 0 | >>>>> >>>>> | | rawDataSize >>>>> | 0 | >>>>> >>>>> | | totalSize >>>>> | 0 | >>>>> >>>>> | | transient_lastDdlTime >>>>> | 1647024660 | >>>>> >>>>> | | NULL >>>>> | NULL | >>>>> >>>>> | # Storage Information | NULL >>>>> | NULL | >>>>> >>>>> | SerDe Library: | >>>>> org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL >>>>> | >>>>> >>>>> | InputFormat: | >>>>> org.apache.hadoop.mapred.TextInputFormat | NULL >>>>> | >>>>> >>>>> | OutputFormat: | >>>>> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL >>>>> | >>>>> >>>>> | Compressed: | No >>>>> | NULL | >>>>> >>>>> | Num Buckets: | -1 >>>>> | NULL | >>>>> >>>>> | Bucket Columns: | [] >>>>> | NULL | >>>>> >>>>> | Sort Columns: | [] >>>>> | NULL | >>>>> >>>>> | Storage Desc Params: | NULL >>>>> | NULL | >>>>> >>>>> | | serialization.format >>>>> | 1 | >>>>> >>>>> >>>>> +-------------------------------+----------------------------------------------------+----------------------------------------------------+ >>>>> >>>>> 33 rows selected (0.159 seconds) >>>>> >>>>> Now check that in spark-shell again >>>>> >>>>> >>>>> scala> spark.sql("show create table test.etc").show(false) >>>>> >>>>> >>>>> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> >>>>> |createtab_stmt >>>>> >>>>> | >>>>> >>>>> >>>>> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> >>>>> |CREATE TABLE `test`.`etc` ( >>>>> >>>>> * `id` BIGINT,* >>>>> >>>>> * `col1` VARCHAR(30),* >>>>> >>>>> * `col2` STRING)* >>>>> >>>>> USING text >>>>> >>>>> TBLPROPERTIES ( >>>>> >>>>> 'bucketing_version' = '2', >>>>> >>>>> 'transient_lastDdlTime' = '1647024660') >>>>> >>>>> | >>>>> >>>>> >>>>> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> >>>>> >>>>> It shows OK. Soo in summary you get column definitions in Spark as >>>>> you have defined them in Hive >>>>> >>>>> >>>>> In your statement above and I quote "I am using Spark 2.4.1 and >>>>> Beeline 2.1.1", refers to older versions of Spark and hive which may >>>>> have had such issues. >>>>> >>>>> >>>>> HTH >>>>> >>>>> >>>>> >>>>> view my Linkedin profile >>>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> >>>>> >>>>> >>>>> https://en.everybodywiki.com/Mich_Talebzadeh >>>>> >>>>> >>>>> >>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>>>> any loss, damage or destruction of data or any other property which may >>>>> arise from relying on this email's technical content is explicitly >>>>> disclaimed. The author will in no case be liable for any monetary damages >>>>> arising from such loss, damage or destruction. >>>>> >>>>> >>>>> >>>>> >>>>> On Fri, 11 Mar 2022 at 18:19, Venkatesan Muniappan < >>>>> m.venkatbe...@gmail.com> wrote: >>>>> >>>>>> hi Spark Team, >>>>>> >>>>>> I have raised a question on Spark through Stackoverflow. When you get >>>>>> a chance, can you please take a look and help me ?. >>>>>> >>>>>> https://stackoverflow.com/q/71431757/5927843 >>>>>> >>>>>> Thanks, >>>>>> Venkat >>>>>> 2016173438 >>>>>> >>>>>
