polygon-etl | ingesting Polygon blockchain data to Google BigQuery

Using your "next" approach AND assuming the data is generated in ascending line order, the following does work in parallel, but if actually faster you can tell me; I do not know your volume of data. In any event you cannot solve just with SQL (%sql).

Here goes:

For the current structure, instead of creating a document every 15 minutes you can just create a "prices" document and store an array of format { time: "00:00", price: 100 } which will cost only 1 read to fetch prices of a given currency on a day instead of 96.

Alternatively, you can create a single collection "prices" and create a document everyday for each currency. A document in this collection can look like this:

i face the same problem, i think Spark 3.2 is the problem itself

switched to Spark 3.1.2, it works fine

Note that this is not an error, but a log message: https://github.com/tensorflow/tensorflow/blob/42b5da6659a75bfac77fa81e7242ddb5be1a576a/tensorflow/core/kernels/data/shuffle_dataset_op.cc#L138

It seems you may be choosing too large a dataset if it's taking too long: https://github.com/tensorflow/tensorflow/issues/30646

You can address this by lowering your buffer size: https://support.huawei.com/enterprise/en/doc/EDOC1100164821/2610406b/what-do-i-do-if-training-times-out-due-to-too-many-dataset-shuffle-operations

First of all, there are two types of indexes: local and global.

A local index is stored on the same computer as tweet data. For example, you may have 10 shards and each of these shards will have its own index; like word "car" -> sorted list of tweet ids.

When search is run we will have to send the query to every server. As we don't know where the most popular tweets are. That query will ask every server to return their top results. All of these results will be collected on the same box - the one executing the user request - and that process will pick top 10 of of entire population.

Since all results are already sorted in the index itself, it is a O(1) operation to pick top 10 results from all lists - as we will be doing simple heap/watermarking on set number of tweets.

Second nice property, we can do pagination - the next query will be also sent to every box with additional data - give me top 10, with popularity below X, where X is the popularity of last tweet returned to customer.

Global index is a different beast - it does not live on the same boxes as data (it could, but does not have to). In that case, when we search for a keyword, we know exactly where to look for. And the index itself is also sorted, hence it is fast to get top 10 most popular results (or get pagination).

Since the global index returns only tweet Ids and not tweet itself, we will have to lookup tweets for every id - this is called N+1 problem - 1 query to get a list of ids and then one query for every id. There are several ways to solve this - caching and data duplication are by far most common approaches.

This is happening because there is still need for a full table scan to find all the test IDs that are equal to the specified one.

It is not clear from your example which columns are part of the timeseries record. In case test_id is not one of them, I would suggest to cluster the table on the test_id column. By clustering, the data will be automatically organized according to the contents of the test_id column.

So, when you query with a filter on that column a full scan won't be needed to find all values.

Read more about clustered tables here.

I don't think using Pandas is helping here as you are just comparing whole lines. An alternative approach would be to load the first file as a set of lines. Then enumerate over the lines in the second file testing if it is in the set. This will be much faster:

It is just that there are many unused levels as the column 'seqnames' is a factor. With split, there is an option to drop (drop = TRUE - by default it is FALSE) to remove those list elements. Otherwise, they will return as data.frame with 0 rows. If we want those elements to be replaced by NULL, then find those elements where the number of rows (nrow) are 0 and assign it to NULL