Most programmers are familiar with split. It’s one of the ten most commonly used functions in programming. BigQuery has a split function too, but since it’s SQL, the usage is quite different from general-purpose programming languages. On top of that, BigQuery comes in two versions, Standard SQL and Legacy SQL. Both have split, but they are used in completely different ways. ...
In BigQuery, if you want to get the first day from a date (or a timestamp or datetime value), for example turning “2017-05-24” into “2017-05-01”, you can call the UTC_USEC_TO_MONTH function in Legacy SQL, or DATE_TRUNC() in Standard SQL. In fact, BigQuery has a whole series of functions that do these things, getting the first day, hour, week, and so on from a date. ...
When doing data analysis, randomly picking N rows from a dataset (sampling) is a very common need. You might sample to check whether the data is correct, or sample so a model can be built when there is too much data to process comfortably. Sampling with SQL inside the database is actually very simple. Here I’ll demonstrate with BigQuery Standard SQL, but Legacy SQL works the same way. ...
For the past few days I’ve been writing a small program to organize contacts, so I’ve been researching quite a few things. While working on the database side, I wanted to guard against so-called SQL injection, where a malicious string is placed in a parameter to attack the database. So I applied some basic defenses. The following is how to add some basic protection against SQL injection~ ...
Over the past few days, while writing a small program, I ran into a problem. I wanted to put a $_POST variable directly into my SQL query string instead of using the concatenation operator. But because of quoting issues, it took me quite a while to figure out how. Here is a brief introduction. I also want to thank Bu and Cang Shixian for teaching me this method! If it weren’t for them, I might still not know about it. Also, this method isn’t limited to $_POST; it works with other arrays too. ...
Recently, while writing a program in PHP, I ran into some SQL code, so I looked into it a bit and discovered something beginners easily get wrong: the ` and ’ symbols do different things. When I didn’t know this, and the book’s print was small, it took me two hours to find out it was this small problem…. So I wrote this post to tell everyone that these two really are different. ...