orc | Open Research Computing | Code Editor library

This should help you:

if you don't have index on your fk columns , you should add them for sure , here is my suggestion:

XLOOKUP unlike VLOOKUP returns a reference to the cell and not just the value of the cell.

With this in mind =XLOOKUP(D2,A2:C2,A2:C2,NA()) will return the value if it exists as well as the reference.

If we wrap the Return Array with the Column function it will return the column number.
=XLOOKUP(D2,A2:C2,COLUMN(A2:C2),NA())

Add the ADDRESS function to return the cell address (this will return the address on row 1)
=XLOOKUP(D2,A2:C2,ADDRESS(1,COLUMN(A2:C2),4),NA())

Now substitute the 1 in the cell address with a blank: =SUBSTITUTE(XLOOKUP(D2,A2:C2,ADDRESS(1,COLUMN(A2:C2),4),NA()),"1","")

Please consider sorting sequences and joining them with itertools.groupby() or just a generator:

I think we can do this by Providing a custom partition key schema encoded via the source_uri_prefix field.

Using below links and examples [1] & [2] related to Partition Schema detection modes, I think you can do it. [1] https://cloud.google.com/bigquery/docs/hive-partitioned-loads-gcs#command-line-tool [2] https://cloud.google.com/bigquery/docs/hive-partitioned-loads-gcs

Setting this to false helped.

Since I didn't want to build Hadoop by myself, inspired by the workaround used here, I've configured Spark to use Hadoop native libraries:

hive.metastore.warehouse.dir - is a warehouse root directory.

When you create the database, specify MANAGEDLOCATION - a location root for managed tables and LOCATION - root for external tables.

MANAGEDLOCATION is within hive.metastore.warehouse.dir

Setting the metastore.warehouse.tenant.colocation property to true allows a common location for managed tables (MANAGEDLOCATION) outside the warehouse root directory, providing a tenant-based common root for setting quotas and other policies.

See more details in this manual: Hive managed location.

Spark can read a csv file only with one executor as there is only a single file.

Compared to files which are located in a distributed files system such as HDFS where a single file can be stored in multiple partitions. That means your resulting Dataframe df has only a single partition. You can check that using df.rdd.getNumPartitions. See also my answer on How is a Spark Dataframe partitioned by default?

Note that coalesce will collapse partitions on the same worker, so calling coalesce(16) will not have any impact at all as the one partition of your Dataframe is anyway located already on a single worker.

In order to increase parallelism you may want to use repartition(16) instead.