You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@spark.apache.org by Gang Li <lg...@gmail.com> on 2020/09/09 07:42:40 UTC
For the same data source in two SQLs, how to read it once？
Hi all,
I ran two Spark SQL, they read the same table, partition, but write to
different tables. Is there any way to merge them into one SQL, and realize
the read data operation is only run once?
Suppose there are two SQL:
-----------------------------------------------------------------------------------------------------------------
INSERT OVERWRITE TABLE spark_input_test2 PARTITION(dt='20200909')
SELECT name, number, age
FROM spark_input_test WHERE dt='20200908'
-----------------------------------------------------------------------------------------------------------------
INSERT OVERWRITE TABLE spark_input_test1 PARTITION(dt='20200909')
SELECT name, number, sex
FROM spark_input_test WHERE dt='20200908'
-----------------------------------------------------------------------------------------------------------------
Running these two SQL statements will generate two Physical Plan, and the
data source "spark_input_test" will be read twice. If spark_input_test is
read only once, it will save memory.
Cheers,
Gang Li
--
Sent from: http://apache-spark-developers-list.1001551.n3.nabble.com/
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: 回复: For the same data source in two SQLs, how to read it once？
Posted by Gang Li <lg...@gmail.com>.
I will pay attention in the future, thank you very much for your suggestions.
--
Sent from: http://apache-spark-developers-list.1001551.n3.nabble.com/
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
回复: For the same data source in two SQLs, how to read it once？
Posted by Liu Genie <ge...@outlook.com>.
I think Yang Sun means createTempView, which will cache data in memory instead of writing to disk.
And I sugguest this post should be discussed in user@spark.apache.org mail list.
________________________________
发件人: Gang Li <lg...@gmail.com>
发送时间: 2020年9月9日 17:06
收件人: dev@spark.apache.org <de...@spark.apache.org>
主题: Re: For the same data source in two SQLs, how to read it once？
If use a temporary table, the execution process is shown in the following
figure
<http://apache-spark-developers-list.1001551.n3.nabble.com/file/t3738/tmp1.png>
Is there any way to achieve the following figure?
<http://apache-spark-developers-list.1001551.n3.nabble.com/file/t3738/tmp2.png>
thanks！
--
Sent from: http://apache-spark-developers-list.1001551.n3.nabble.com/
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: For the same data source in two SQLs, how to read it once？
Posted by Gang Li <lg...@gmail.com>.
If use a temporary table, the execution process is shown in the following
figure
<http://apache-spark-developers-list.1001551.n3.nabble.com/file/t3738/tmp1.png>
Is there any way to achieve the following figure?
<http://apache-spark-developers-list.1001551.n3.nabble.com/file/t3738/tmp2.png>
thanks！
--
Sent from: http://apache-spark-developers-list.1001551.n3.nabble.com/
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: For the same data source in two SQLs, how to read it once？
Posted by Gang Li <lg...@gmail.com>.
Writing to the temporary table does allow the data source to read once, but
writing to the temporary table will have disk I/O operations, and there is
no effective use of Spark RDD's memory-based operations
<http://apache-spark-developers-list.1001551.n3.nabble.com/file/t3738/tmp1.png>
--
Sent from: http://apache-spark-developers-list.1001551.n3.nabble.com/
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: For the same data source in two SQLs, how to read it once？
Posted by Yang shun <at...@gmail.com>.
>
> You can do this by creating a temporary table.
>
1.Ensure that all fields are included and cached as a dataset when the data
is first pulled(age、sex、other...)
2.When outputting to different tables, select different fields of the
cached dataset.