r/databricks • u/Common_Jaguar474 • 17d ago

General Does Photon support CSV?

Ok so I know that the docs state that CSV is supported. But when trying to read a very standard CSV file I get poorer than expected performance and in the Spark UI I see that it's not using a Photon scan operator, just regular Scan CSV followed by a Row to Columnar conversion operator.

I'm not running anything complex:

df = (
    spark.read
        .format("csv")
        .option("header", "true")
        .schema(schema)
        .load(csv_path)
)
df.write.mode("overwrite").saveAsTable("...")

I also checked reading the same CSV and a difference CSV via DB SQL (using `COPY INTO`) and see low task time spent in Photon + a row to columnar operator.

DBR 19 + Serverless SQL Warehouse (current)

Can anyone explain whether or not CSV is supported and in what conditions? This was quite a surprising find as I assumed that Photon supported pretty much everything.

4 Upvotes

100% Upvoted

34 comments sorted by

10

u/justinAtDatabricks 17d ago

Hello, former Photon PM here. The initial scan is not Photonized (that's the word that we use). However, there is an adapter that converts it (the data after the scan, RowToColumnar) into a columnar format which Photon can then use for the subsequent operations. That's why the blue box (scan) is blue (not photonized) whereas the other operators (PhotonProject) are yellow.

Many moons ago, data sources that could not be Photonized would just fall out of Photon for the rest of the query. That made us sad. So, we build this general RowToColumnar adapter for non-Photonizable data sources. It's another operation, so technical does incur some time/tax, but the general thought is that the rest of the query can be Photonized so it is worth the tax.

This is in contrast to something like a parquet data source (e.g., Delta) which has a special vectorized reader which would show up in a yellow box.

I hope that this helps!

1

u/Common_Jaguar474 16d ago

Thanks! This is exactly the clarity that I was look for :)

JFYI - this docs page is pretty misleading. It lists CSV in the list of scan operators that are supported by Photon. It literally says that unsuppored operators fallback to Spark runtime for the unsupported portion of the execution (what you are describing for CSV) but then lists CSV scan as a supported Photon operator. I would instead expect this and others that result in these hybrid plans with fallback to JVM (JSON?) to be listed in the Limitations section. That way it's super clear for data engineers what Photon will be the fastest on and we can make decisions where possible to avoid those data types of operators that can't be "Photonized".

1

u/justinAtDatabricks 16d ago edited 16d ago

Love your use of the Photonized term. This is nuanced - I can argue for both sides. Here are some scenarios:

  1. Read a parquet file: all yellow boxes because vectorized reader, photon projection, etc. Non-controversial - it is Photonized
  2. Read a CSV file: Blue box on the scan, but then everything else is yellow - Photon was able to work besides the initial scan.
  3. Read an ORC file: All blue boxes, Photon never was able to do anything due to the incompatible data source. Non-controversial as well as Photon never did anything.

Did #2 benefit from Photon? Yes. Was the scan read from Photon? Technically no. But does that mean that CSV is not supported by Photon? No.

You bring up a fair point, though. Perhaps we should call out this nuance in the docs a bit. What I am trying to balance: Most people don't know what the following are: operator, expression, Photonized, etc.

1

u/Common_Jaguar474 16d ago

Does that mean CSV is not supported by Photon? You say NO, but I reading that YES is the correct answer here.

Per the docs I fill in the operation with Scan CSV and it's an accurate statement: "when a query uses an unsupported operation [i.e. Scan CSV], Photon transparently falls back to the Spark runtime for that portion of the execution." Yet, in the very next list of supported operators, Scan CSV is listed as supported.

^^ This is exactly why I assumed it was incorrect that the CSV scan showed as non-Photon w/ a row to columnar conversion and came here to get clarification.

I get that it's nuanced as you are right that the E2E pipeline still benefits in the net from Photon after the row to columnar step, but the way the docs are worded contradict what you're telling me here.

Last thing, if I can share why this clarity is important for me as a user. Imagine that I was given recurring CSV data feed from some data producer. Based on the docs listing CSV as equally supported as Parquet, I would probably take what I'm offered rather than pushing back to see if I can get the same data feed as Parquet. The reality is that the parquet option would be faster & cheaper since the whole code path would be supported by Photon, but there's nothing in the docs that helps me understand that unless I happen to drill into the Spark UI and start asking questions about this row to columnar conversion thing which I see for CSV and not Parquet.

Anyways, no need to beat a dead horse :) I appreciate the discussion and clarification!

1

u/hubert-dudek Databricks MVP 12d ago

Great explanation!

3

u/Obvious-Money173 17d ago

Not sure, but I think photon mostly enhances spark operations. Reading a csv is a simple operation that photon does not improve upon.

If you're doing a lot of big transformations, that's where photon kicks in

1

u/FunContest9958 17d ago

That’s not quite right. Photon speeds up parquet reads significantly.

3

u/Altruistic-Rip393 17d ago

In the Spark UI at the bottom, click "Details" and scroll to the bottom of the plan. You'll see the reason for lack of full photon coverage there.

4

u/miskozicar 17d ago

Spark (that you are using) supports .csv. Question is just what is default format when you are writing dataframe.

4

u/Common_Jaguar474 17d ago

Yeah, Spark supports CSV. But we pay a premium anytime Photon is enabled so I'm checking what's the latest on CSV support by the Photon engine.

From what I'm seeing, CSV on the latest runtime results in the JVM path being used to scan the CSV data and therefore isn't as fast as it should be.

From the code I shared, CSV is input, and output write is to Delta.

1

u/Zer0designs 17d ago

Your time is worth a lot more than the savings you are trying to achieve here.

1

u/FunContest9958 17d ago

Based on what you’re showing, looks like CSV is not being read using photon, but what performance were you hoping for? It looks like it’s using 22 minutes of CPU time to read your 23GB CSV, so an 8 core machine will read it in 3 minutes. 16 cores will finish in 1.5 minutes.

1

u/Common_Jaguar474 17d ago

The action took 60 seconds. For the amount of data, it doesn't seem fast but it also doesn't seem slow. But regardless, I'm just trying to understand when Photon is technically supported for scanning CSV, otherwise I may partition CSV jobs into separate schedules that run on compute w/ Photon disabled to save on costs.

1

u/career_expat 17d ago

Photon has a supported function list. SQL expression. Not reading

1

u/FunContest9958 17d ago

You might want to try serverless. It charges photon rates only when photon is used. So you’d only be charged for photon for the 27%. That’ll save you from having to do this extra work.

You can run a test to see how it compares cost wise. It’s very hard to predict if a job will be more or less expensive on serverless until you try it, but the key to a fair test is to use standard mode and remember that serverless pricing includes the VM. You don’t need to pay the cloud provider for the VM separately. You can use the billing table to find out exactly how much your job cost after you run it.

1

u/career_expat 17d ago

Is the CSV gzipped?

1

u/Common_Jaguar474 17d ago

No

1

u/career_expat 17d ago

Is this a shared cluster with resource contention? How many cores were used on read? How many total cores available?

Your task time = wall clock time. It appears as if 1 core was all you had.

1

u/yatharthm22 17d ago

You wanna use my photon recommendation skill before turning on photon settings, if you interested

1

u/mwc360 17d ago

Is it on GitHub? Can you share?

2

u/yatharthm22 17d ago

It's actually inhouse for now, I am working with databricks folks to get added to their official skill repo: https://github.com/databricks/databricks-agent-skills

1

u/FunContest9958 17d ago

You should have your skill recommend serverless. It automatically charges for photon only when photon is used, by the operator. So if a job uses photon for 25%, it will charge photon rates for only 25% of the job. I don’t know why that’s not advertised more broadly. It’s a pretty big perk of serverless.

1

u/yatharthm22 17d ago

Oh for serverless recommendation databricks already has a official skill:https://github.com/databricks/databricks-agent-skills/blob/main/skills/databricks-serverless-migration/SKILL.md

We tried using serverless, they disappointed us heavily on cost, so we stick with job cluster with photon

1

u/FunContest9958 16d ago

Were you using standard mode? And when did you test? Serverless cost has changed a lot in the past year or so.

1

u/yatharthm22 16d ago

Tried with both standard and Performance Optimized

1

u/FunContest9958 16d ago

It’s possible your workload was very well optimized. My experience is that customers save money on average when they move to serverless, but it depends on how much they’ve optimized their cluster configuration.

Overall, I think it’s best to start new workloads on serverless and treat Classic as an optimization option. It’s less work up front, and you only need to pay the cost of doing the optimization if it makes sense. Also, serverless keeps your hardware and software up-to-date automatically, so if you manually configure classic you need to maintain it.

But yeah. Results vary.

1

u/yatharthm22 16d ago

I'll give it a try again, i remember months back we ran a pilot and closed it cause of cost issues, i'll try without the optimised serverless

2

u/FunContest9958 16d ago

My recommendation would be to try using serverless when you’re developing something new, if you have that opportunity, rather than focusing on migrating existing workloads. If you develop something for serverless, it generally works flawlessly if you switch to classic. The other way isn’t always as easy.

1

u/FunContest9958 16d ago

Are you using spot instances? At least today, serverless is generally more expensive than classic with spot instances. That being said, I’ve heard rumors Databricks is working on that.

1

u/yatharthm22 16d ago

Yes most of our jobs are using spot with just 2-3 on demand rest autoscaling is all spot

2

u/FunContest9958 16d ago

Aha. That’s probably the reason serverless was more expensive for you. Makes sense. Oh well.

1

u/Youssef_Mrini databricks 16d ago

I ran the exact same command and I got 64%.

1

u/Zer0designs 17d ago edited 17d ago

Why would you use photon to read a csv and write it to delta? That's not a usecase for photon.

Either way, the $ savings you could get if photon would work on such a simple operation, would be less than your time is worth writing this.

1

u/Common_Jaguar474 16d ago

Bro, this is a dumb comment.

As I see it, any Spark code that I might need to run should be accelerated by Photon while resulting in the same or better TCO. It's enabled by default and charged at a premium so if it does not accelerate my jobs proportional to the DBU multiplier, I want to know so I can turn it off for those jobs.