-- create the hivecontext
scala>
*val HiveContext = new org.apache.spark.sql.hive.HiveContext(sc)*HiveContext:
org.apache.spark.sql.hive.HiveContext =
org.apache.spark.sql.hive.HiveContext@6387fb09
--use the test dastabase
scala>
*HiveContext.sql("use test")*res8: org.apache.spark.sql.DataFrame =
[result: string]
-- create a table
scala>
*var sqltext: String = ""*sqltext: String = ""
*scala> sqltext = | """ | CREATE TABLE IF NOT EXISTS
test.sales2 | ( | PROD_ID bigint
, | CUST_ID bigint , |
TIME_ID timestamp , | CHANNEL_ID
bigint , | PROMO_ID
bigint , | QUANTITY_SOLD
decimal(10) , | AMOUNT_SOLD decimal(10) |
) | CLUSTERED BY (PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID) INTO 256
BUCKETS | STORED AS ORC | TBLPROPERTIES (
"orc.compress"="SNAPPY", | "orc.create.index"="true", |
"orc.bloom.filter.columns"="PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID",
| "orc.bloom.filter.fpp"="0.05", | "orc.stripe.size"="268435456", |
"orc.row.index.stride"="10000") | """*
scala>
*HiveContext.sql(sqltext)*res12: org.apache.spark.sql.DataFrame = [result:
string]
-- show this table schema
scala>
*HiveContext.sql("desc sales2").show*+-------------+-------------+-------+
| col_name| data_type|comment|
+-------------+-------------+-------+
| prod_id| bigint| null|
| cust_id| bigint| null|
| time_id| timestamp| null|
| channel_id| bigint| null|
| promo_id| bigint| null|
|quantity_sold|decimal(10,0)| null|
| amount_sold|decimal(10,0)| null|
+-------------+-------------+-------+
--Add a new column to this table
scala>
*HiveContext.sql("ALTER TABLE test.sales2 add columns (new_col
varchar(30))")*res17: org.apache.spark.sql.DataFrame = [result: string]
scala>
*HiveContext.sql("desc sales2").show*+-------------+-------------+-------+
| col_name| data_type|comment|
+-------------+-------------+-------+
| prod_id| bigint| null|
| cust_id| bigint| null|
| time_id| timestamp| null|
| channel_id| bigint| null|
| promo_id| bigint| null|
|quantity_sold|decimal(10,0)| null|
| amount_sold|decimal(10,0)| null|
| new_col| varchar(30)| null|
+-------------+-------------+-------+
HTH
Dr Mich Talebzadeh
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*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 26 June 2016 at 13:34, pseudo oduesp <[email protected]> wrote:
> Hi,
> how i can alter table by adiing new columns to table in hivecontext ?
>
>
