โ03-03-2023 03:03 AM
how to fetch the top 1L rows from a long SQL query as the query is returning 5gb data, I want to check only the first 1L ROWS.
โ03-10-2023 12:54 AM
@KVNARK .โ Hi, I could see this question is already answered. But this is something I found, Please check.
By default, Databricks SQL notebooks limit the number of rows displayed to 1,000. However, you can increase this limit to display more than 1 Lakh (100,000) rows by changing the configuration setting for spark.databricks.query.displayMaxRows
.
To change the configuration setting, follow these steps:
Thanks,
Vignesh
โ03-03-2023 04:17 AM
By 1L do you mean 100,000? You could do a limit in your SQL query.
โ03-03-2023 05:21 AM
As Josephk stated you can use limit it's the easiest solution
โ03-03-2023 07:12 AM
SELECT * FROM t LIMIT 10000;
โ03-05-2023 08:19 PM
I'm not asking about a single table. My query is I have a long SQL query which will generate the out put as 5gb data. Out of which I need tolimit the rows only 1L.
select * from (my long sql query) A order by A.X limit 100000:
This is not working.
โ03-06-2023 06:51 AM
Can you clarify "not working", please.
is there an error of sorts? Which one? OR is it simply "slow" and you expected it to be faster given the LIMIT clause?
โ03-06-2023 09:29 PM
Not getting any result if I give like above. But when given like below, I'm getting the output.
select * from (my long sql query) A
โ03-07-2023 11:32 AM
You get an empty result set? May that should be a support ticket.
Cleary adding an ORDER BY may make things slower, but it should not change the resut set.
Can you try only ORDER BY without LIMIT? Just t see where the problem is..
Also try LIMIT without ORDER BY
โ03-09-2023 02:03 AM
Did you tried to see if you are getting output with less limit count.
Ex select * from (my long sql query) A order by A.X limit 100;
This can help understand if the underlying subquery is a problem or with the volume trying to display
โ03-09-2023 09:05 PM
@Serge Rielauโ both the ways I tried but its of no use. its giving 10000 rows max but note more than that.
โ03-10-2023 12:54 AM
@KVNARK .โ Hi, I could see this question is already answered. But this is something I found, Please check.
By default, Databricks SQL notebooks limit the number of rows displayed to 1,000. However, you can increase this limit to display more than 1 Lakh (100,000) rows by changing the configuration setting for spark.databricks.query.displayMaxRows
.
To change the configuration setting, follow these steps:
Thanks,
Vignesh
โ03-10-2023 02:52 AM
@Vigneshraja Palanirajโ - Perfect. Thanks a lot!
โ03-11-2023 07:36 PM
use the below commands
df.show(100000)
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโt want to miss the chance to attend and share knowledge.
If there isnโt a group near you, start one and help create a community that brings people together.
Request a New Group