Sreate temp table or view by using location

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Sreate temp table or view by using location

霍战锋
Hi,

I'm trying to use spark SQL to define a temp table which can be destroyed automatically with the session.  But when I using the SQL as below, I can't query any valid row, meanwhile, it works when I delete the word 'temp'.  Is there anyone can tell me how to write the right SQL?
It doesn't work like this one.
spark.sql("create temp view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true') location 'src/main/resources/people.txt'")
It works like this one, but it's not a temp table.
spark.sql("create view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true') location 'src/main/resources/people.txt'")
As a repetition, I would like to use SQL to get the same result as 'dataset.createOrReplaceTempView(table name)'.

Thank you.

Best Regards
Reply | Threaded
Open this post in threaded view
|

Re: Sreate temp table or view by using location

霍战锋
Sorry for the typo. The title is 'Create temp table or view by using location'.
Best Regards


霍战锋 <[hidden email]> 于2019年8月13日周二 下午8:00写道:
Hi,

I'm trying to use spark SQL to define a temp table which can be destroyed automatically with the session.  But when I using the SQL as below, I can't query any valid row, meanwhile, it works when I delete the word 'temp'.  Is there anyone can tell me how to write the right SQL?
It doesn't work like this one.
spark.sql("create temp view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true') location 'src/main/resources/people.txt'")
It works like this one, but it's not a temp table.
spark.sql("create view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true') location 'src/main/resources/people.txt'")
As a repetition, I would like to use SQL to get the same result as 'dataset.createOrReplaceTempView(table name)'.

Thank you.

Best Regards
Reply | Threaded
Open this post in threaded view
|

Re: Sreate temp table or view by using location

霍战锋
It works by using "create temp view" and "options(path='something')" together, thanks.
spark.sql("""create temp view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true',path='src/main/resources/people.txt')""")
Best Regards


霍战锋 <[hidden email]> 于2019年8月13日周二 下午8:03写道:
Sorry for the typo. The title is 'Create temp table or view by using location'.
Best Regards


霍战锋 <[hidden email]> 于2019年8月13日周二 下午8:00写道:
Hi,

I'm trying to use spark SQL to define a temp table which can be destroyed automatically with the session.  But when I using the SQL as below, I can't query any valid row, meanwhile, it works when I delete the word 'temp'.  Is there anyone can tell me how to write the right SQL?
It doesn't work like this one.
spark.sql("create temp view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true') location 'src/main/resources/people.txt'")
It works like this one, but it's not a temp table.
spark.sql("create view people (name string, age int) using csv options(sep=',',inferSchema='true',ignoreLeadingWhiteSpace='true') location 'src/main/resources/people.txt'")
As a repetition, I would like to use SQL to get the same result as 'dataset.createOrReplaceTempView(table name)'.

Thank you.

Best Regards