topic Re: Cannot up cast sizeInBytes from string to bigint in Data Engineering

Cannot up cast sizeInBytes from string to bigint

brickster — Sat, 31 Aug 2024 14:50:35 GMT

I am creating a basic delta table using CREATE SQL query

CREATE TABLE test_transact (transaction_id string, post_date date)

and running this query throws an error "Cannot up cast sizeInBytes from string to bigint"

Even if I try to create a dataframe and save as table,

df.write.format("delta").mode("overwrite").saveAsTable("test_transact")

same error appears, despite there is no column sizeInBytes being used.

However, I can able to create temp view using df.createOrReplaceTempView("test_transact")

My cluster DBR version is 14.3 LTS ML (Spark 3.5.0, Scala 2.12)

Can anyone encountered such issue? Appreciate you help... here is the screenshot

Re: Cannot up cast sizeInBytes from string to bigint

nickmerritt — Sun, 01 Sep 2024 00:39:28 GMT

Without being able to see the previous cells in the notebook, it's not possible to pinpoint your exact root cause, but the error indicates that the issue lies with an implicit cast of transaction_id. sizeInBytes is not being referred to as a field, it is a field attribute base on data type, that cannot be manipulated by the cited SQL statement or df method for creating a Table. Per your report, the df method that instantiates a View is more flexible. For the purpose of Table creation, the verbose error is suggesting that you attend to your casting explicitly at ingestion (which I assume is happening in an earlier cell in the same notebook).

https://docs.databricks.com/en/error-messages/error-classes.html#cannot_up_cast_datatype

Re: Cannot up cast sizeInBytes from string to bigint

nickmerritt — Sun, 01 Sep 2024 01:30:39 GMT

It looks like there's an issue between the datatype of a field between the source and the target. Perhaps in your CREATE you are implicitly casting that transaction_id? I'm just going off of similar discussions here in the community, and I cannot speak to the cells before screenshot cell in your notebook, but your verbose error message wants you to explicitly cast something (I suspect transaction_id) prior to the CREATE statement, i.e. earlier in your notebook, at ingestion.

https://docs.databricks.com/en/error-messages/error-classes.html#cannot_up_cast_datatype

Re: Cannot up cast sizeInBytes from string to bigint

Brahmareddy — Sun, 01 Sep 2024 04:10:10 GMT

Hi @brickster, How are you doing today?

As per my understanding, Try dropping any existing table or metadata related to test_transact before creating it to avoid conflicts. Consider using a different table name or creating it in a new schema to bypass potential metadata issues. Explicitly define the schema in your DataFrame to prevent type inference problems. Restart your cluster to clear any cached sessions or metadata causing the error. Lastly, check for DBR version compatibility with Delta Lake, and consider trying a different DBR version if the issue persists.

Just give a try and let me know if it works. Good day.

Regards,

Brahma

Re: Cannot up cast sizeInBytes from string to bigint

Brahmareddy — Sun, 01 Sep 2024 04:11:55 GMT

Hi @brickster, How are you doing today?

Just give a try and let me know if it works. Have a good day.

Regards,

Brahma

Re: Cannot up cast sizeInBytes from string to bigint

Brahmareddy — Sun, 01 Sep 2024 04:13:14 GMT

Hi @brickster, How are you doing today?

Try dropping any existing table or metadata related to test_transact before creating it to avoid conflicts. Consider using a different table name or creating it in a new schema to bypass potential metadata issues. Explicitly define the schema in your DataFrame to prevent type inference problems. Restart your cluster to clear any cached sessions or metadata causing the error. Lastly, check for DBR version compatibility with Delta Lake, and consider trying a different DBR version if the issue persists.

Give a try.

Regards,

Brahma

Re: Cannot up cast sizeInBytes from string to bigint

filipniziol — Sun, 01 Sep 2024 09:59:56 GMT

Hi @brickster ,

The error message in the screenshot indicates that there is an issue with casting sizeInBytes from STRING to BIGINT related to the SnapshotState in Delta Lake. This is not caused by the columns you are trying to create in your Delta table but rather relates to internal metadata managed by Delta Lake.

What it means is that most likely the metadata of Delta table is corrupt. For example you created the table, then you dropped it, but still there are some leftover files in the table location.

You need to clean-up the table location before recreating it to make sure there are no older files anymore.

Here are the steps:
1. Table creation (this is already done):

%sql CREATE TABLE test_transact (transaction_id string, post_date date)

2. Check the table location

%sql DESCRIBE DETAIL test_transact;

-> this is what you want to clean-up before recreating the table

3. Drop the table -> if the metadata is corrupt, some files will not be removed

%sql DROP TABLE IF EXISTS test_transact

4. Show the content of the location (copy-paste from point 2.)

display(dbutils.fs.ls("dbfs:/user/hive/warehouse/test_transact"))

If the metadata is corrupt, you will see some files there even after DROP TABLE:

5. Clean-up the TABLE location:

dbutils.fs.rm("dbfs:/user/hive/warehouse/test_transact", recurse=True)

6. Try now to recreate the table and insert the data.

Re: Cannot up cast sizeInBytes from string to bigint

filipniziol — Sun, 01 Sep 2024 10:01:54 GMT

Hi @brickster ,

The error message in the screenshot indicates that there is an issue with casting sizeInBytes from STRING torelated to the SnapshotState in Delta Lake. This is not caused by the columns you are trying to create in your Delta table but rather relates to internal metadata managed by Delta Lake.

What it means is that most likely the metadata of Delta table is corrupt. For example you created the table, then you dropped it, but still there are some leftover files in the table location.

You need to clean-up the table location before recreating it to make sure there are no older files anymore.

Here are the steps:
1. Table creation (this is already done):

%sql CREATE TABLE test_transact (transaction_id string, post_date date)

2. Check the table location

%sql DESCRIBE DETAIL test_transact;

-> this is what you want to clean-up before recreating the table

3. Drop the table -> if the metadata is corrupt, some files will not be removed

%sql DROP TABLE IF EXISTS test_transact

4. Show the content of the location (copy-paste from point 2.)

display(dbutils.fs.ls("dbfs:/user/hive/warehouse/test_transact"))

If the metadata is corrupt, you will see some files there even after DROP TABLE:

5. Clean-up the TABLE location:

dbutils.fs.rm("dbfs:/user/hive/warehouse/test_transact", recurse=True)

6. Try now to recreate the table and insert the data.