topic Re: Fetching top 1 L rows in Databricks SQL in Data Engineering

Fetching top 1 L rows in Databricks SQL

KVNARK — Fri, 03 Mar 2023 11:03:25 GMT

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.

Re: Fetching top 1 L rows in Databricks SQL

Anonymous — Fri, 03 Mar 2023 12:17:29 GMT

By 1L do you mean 100,000? You could do a limit in your SQL query.

Re: Fetching top 1 L rows in Databricks SQL

youssefmrini — Fri, 03 Mar 2023 13:21:52 GMT

As Josephk stated you can use limit it's the easiest solution

Re: Fetching top 1 L rows in Databricks SQL

SergeRielau — Fri, 03 Mar 2023 15:12:32 GMT

SELECT * FROM t LIMIT 10000;

IMIT clause in the docs

Re: Fetching top 1 L rows in Databricks SQL

KVNARK — Mon, 06 Mar 2023 04:19:12 GMT

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.

Re: Fetching top 1 L rows in Databricks SQL

SergeRielau — Mon, 06 Mar 2023 14:51:50 GMT

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?

Re: Fetching top 1 L rows in Databricks SQL

KVNARK — Tue, 07 Mar 2023 05:29:44 GMT

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

Re: Fetching top 1 L rows in Databricks SQL

SergeRielau — Tue, 07 Mar 2023 19:32:25 GMT

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

Re: Fetching top 1 L rows in Databricks SQL

pvignesh92 — Thu, 09 Mar 2023 10:03:08 GMT

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

Re: Fetching top 1 L rows in Databricks SQL

KVNARK — Fri, 10 Mar 2023 05:05:02 GMT

@Serge Rielau both the ways I tried but its of no use. its giving 10000 rows max but note more than that.

Re: Fetching top 1 L rows in Databricks SQL

pvignesh92 — Fri, 10 Mar 2023 08:54:44 GMT

@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:

In the Databricks workspace, navigate to the cluster that you're using for your notebook.
Click on the "Advanced Options" tab.
In the "Spark" section, click on the "Edit" button next to "Spark Config".
Add the following configuration setting:
spark.databricks.query.displayMaxRows 10000 (or the desired maximum number of rows)
Click "Confirm" to save the configuration change.

Thanks,

Vignesh

Re: Fetching top 1 L rows in Databricks SQL

KVNARK — Fri, 10 Mar 2023 10:52:08 GMT

@Vigneshraja Palaniraj - Perfect. Thanks a lot!

Re: Fetching top 1 L rows in Databricks SQL

Sangram — Sun, 12 Mar 2023 03:36:58 GMT

use the below commands

df.show(100000)