DEV Community: Uri Shaked

Exporting Google Cloud Storage File List to BigQuery (CSV)

Uri Shaked — Thu, 30 Jul 2020 12:18:36 +0000

Sometimes you need to run complex queries against the list of files storage in your Google Cloud Storage. In my case, I wanted find out which files were no longer referenced by my database and clean them up.

Here is how I did it:

Exporting the File List to a CSV file

This simple 1-line script will allow you to the entire file list from Google Cloud Storage to a simple CSV format that can be then loaded into Google BigQuery:

gsutil ls -l gs://bucket-name/** | head -n-1 | awk 'BEGIN{print "Size,Modified,Path"}{print $1","$2",\""$3"\""}' > filelist.csv

Make sure to replace bucket-name with your actual GCS bucket name.

Loading the CSV file to BigQuery

Before you can load the CSV File to Google's BigQuery, you first need to upload it to Google Cloud storage. You can do it by running:

gsutil cp filelist.csv gs://some-bucket/filelist.csv

Then, you can load it right into bigquery:

bq load --autodetect --source_format=CSV mydataset.mytable gs://some-bucket/filelist.csv

Make sure you change some-bucket to your actual bucket name, and mydataset and mytable to the target BigQuery dataset and table, respectively.

I tested this process for a bucket containing about 350,000 files and it worked flawlessly.

!x && (x !== false) && (x !== 0) && (x !== '') && (x !== null) && (x !== undefined)

Uri Shaked — Tue, 23 Jul 2019 16:18:48 +0000

JavaScript never ceases to surprise me. This time, a friend shared the following riddle in the JavaScript Israel community:

Which value of x will cause the following statement to be true?

!x && (x !== false) && (x !== 0) && (x !== '') && (x !== null) && (x !== undefined)

Basically, we want to find a value that is falsy in JavaScript (in other words, !x is true), but is not false, null, undefined, zero or the empty string.

Take a moment to think of an answer. Then, scroll down for my answer and other answers suggested by the community.

.
.
.
.
.
.
.
.
.
.
.
.
.
.

My Answer

My approach was to make x evaluate to a different value each time, so it will be falsy when !x is evaluated, but will pass all the other tests.

Thus, I defined a getter for x on the window object, incrementing the returned value upon each call:

var counter = 0;
Object.defineProperty(window, 'x', { get: () => counter++ });

Since x starts with zero and increments on each read, the original expression becomes:

!0 && (1 !== false) && (2 !== 0) && (3 !== '') && (4 !== null) && (5 !== undefined)

which evaluates to true!

Even More Answers

While I was typing my answer, someone has already found a very simple answer: x should be NaN. This value is falsy, and is indeed not equal to any of the the other values in the expression. You can also verify this by looking at the definition of toBoolean in the ECMAScript standard.

Another answer, which really surprised me, was document.all. If you are curious to know why document.all is indeed falsy, check out this StackOverflow answer. Legacy reasons, amazing!

Finally, the answer that the original author of this riddle was looking for, is BigInt(0). If you haven't heard of this before, don't worry - it's still not an official part of the JavaScript language, but will probably be soon, as part of the BigInt proposal. If you are interested, MDN has a pretty good documentation about BigInt.

What I love about this riddle, is how diverse the solutions are: one exploits the dynamic nature of JavaScript using Object.defineProperty, another solution uses legacy feature (document.all), while the BigInt solution uses a feature which has not yet introduced into the language (but is already available in Chrome). Pure fun, isn't it?

cURL, the Cloud, 20GB files and I!

Uri Shaked — Sun, 30 Jun 2019 09:21:41 +0000

A quick tip how to use the cloud to transfer humongous files between different cloud services using cURL!

For the past few years, I have been co-organizing several meetups, including JavaScript Israel and IoT Makers Israel. I am a firm believer in recording all the talks and sharing them on YouTube/Vimeo.

For some reason, the video guys sometimes like to share the raw video files using random file sharing services such as sendgb.com, filemail.com, and similar. These files are usually several gigabytes large, and these services will only keep them for a few days.

Since my home upload is not very fast, I was looking for a quick solution that would enable me to transfer these files to a different location, such as google cloud storage or Vimeo, without having to download them to my machine first and then re-upload them to their new home.

After some fiddling, I found a nice trick that makes this super-simple:

I go to the file sharing service website in Chrome on my machine, and open the developer tools. Then I click on "Download".
I look at the "Network" tab of Chrome's developer tools, and find the specific line that was triggered by my download request. I right click on it, choose "Copy" and "Copy as cURL (bash)". The copied command includes all the relevant cookies, headers, and any submitted form data, making it easy to run the same HTTP request on a different machine and get the same file.
Finally, I SSH to one of my cloud machines, paste the cURL command that I just copied, and pipe its output to a second command that will upload the file to the desired service. See some examples below.

Copying the file to Google Cloud Storage or AWS

For copying the file to GCS, I use the gsutil command from any Google Cloud VM (the VM needs to have write permissions for GCS), or for smaller files (<3GB), I just fire up the Cloud Shell. The result looks like:

curl ... | gsutil cp - gs://bucket-name/filename.mp4

Where curl ... is the command you copied from Chrome Devtools, bucket-name is the target GCS bucket you want to copy that file to, and filename.mp4 is what you want to call the file.

If you work on AWS, you can use the aws s3 command to achieve a similar result:

curl ... | aws s3 cp - s3://bucket-name/filename.mp4

Uploading the file directly to Vimeo

I recently found out that Vimeo support FTP uploads on their paid plans. Thus, I use a similar method, but this time I pipe the output of curl into a different curl process that uploads it through FTP:

curl ... | curl -T - ftp://user:PASS@ftp-3.cloud.vimeo.com/video.mp4

You can use this method to upload the file to any ftp server - just replace the user, PASS and ftp-3.cloud.vimeo.com with the relevant values for your FTP account.

Uploading to other places - Google Drive, Dropbox, etc.

You can easily extend this method and upload the files to your favorite cloud provider. For instance, for Google Drive, you can use the gdrive cli and pipe the cURL output to gdrive upload - <path>.

For Dropbox, you can follow the steps in this StackOverflow answer to create a cURL command that will upload the given file to dropbox. You will only need to change --data-binary @matrices.txt into --data-binary @- so that the commands gets it input from stdin, that is the output of other curl command you pipe into it.

Summary

The trick presented here is quite straightforward - thanks to Chrome's DevTools, you can easily convert any network request your browser makes into a cURL command you can run anywhere and in most cases will produce the same result. I leveraged this method in order to transfer large files using Linux commands on the cloud, but I am sure you can find even more creative use cases. When you do, please share them with me :-)

My First Dev.To Post

Uri Shaked — Mon, 03 Jun 2019 13:06:32 +0000

What should my next post be about? 🤔