One employee walks up to you and says “oh well hey we have a new beta program for you, and you’ll only need to wait 1 minute instead of 3 hours.”
Another employee appears out of nowhere and chimes in “hey yo 1 minute is not good enough for our standard. We can’t let anyone use it for now. Please go back to the 3-hour queue.”
I’ve been thinking about this a lot and had many interactions with users where they begged to use a subpar, sometimes unfinished, sometimes paid, feature because it would’ve saved them hours of their lives.
If I were to be honest with the users during a Zoom call, I wouldn’t have been able to reject them in good conscience.
“Play pretend to be honest to users in a face-to-face convo” should be a product development principle.
When building a web application on Playframework, there is no good way to integrate a JS framework like Svelte.
The only way is to run Play and run the npm watch in 2 separate terminals.
One is to build the backend code, and the other is to build the frontend code. It feels brittle.
sbt-svelte is offering a better and slicker way to develop with Playframework and Svelte.
Playframework has a mechanism to watch the JS code and hot-reload during sbt run. Specifically, com.typesafe.sbt.web.incremental.syncIncremental((Assets / streams).value.cacheDirectory / "run", sources)
enables us to monitor all changes occurring under /app/assets.
sbt-svelte utilizes this mechanism to monitor all *.svelte file changes and invoke Webpack to compile only those changed files.
This means you just run sbt run, and that’s it. All your Svelte changes will be compiled when you reload the page. There’s no separate process to run. This works with sbt stage as well.
Then, you can include the JS file into your HTML file and make the component full-SPA, hybrid, or non-SPA. It’s up to you.
It’s just so elegant.
As a side note, sbt-svelte is built in the same way sbt-vuefy (for Vue.js) is built.
sbt-vuefy has been used by GIVE.asia for years now, so we are confident it is robust.
While you visit GIVE.asia, please also consider donating!
I’d love for you to try it out. You can take a look at the test project to see how it works.
Bonus - Why using Svelte over React / Vue?
If you look through a lot of materials online,
I’ve deduced that Svelte is more suitable for mobile web due to a couple reasons:
The size is probably smaller. It doesn’t require a separate runtime.
It’s probably faster for simpler use cases because it compiles to vanilla JS and doesn’t operate on virtual DOM.
React / Vue, on the other hand, requires a runtime and operates on virtual DOM.
If I understand it correctly, React and Vue manipulate virtual DOM and diff against real DOM. For complex use cases, this allows React to batch multiple changes and might result in better performance.
At the company I work at, we are building an analytics product on top of Presto, which supports SQL as its interface. We have a lot of long SQL statements to power charts, tables, and CSVs. When I say long, a final SQL statement is likely >2,000 lines.
One major pain point arises. There are common parts that can be re-used by multiple SQL statements. It would be annoying to keep duplicate these common parts, so we currently solve this pain point by refactoring certain parts into Ruby’s ERBs. However, this is extremely brittle because SQL is a declarative language. It requires the common part to be aware of the columns of the previous statements.
Consider the below example where there are 3 reports:
MRR report that has the following columns: month, mrr_amount, customer_id, customer_name
Payment report that has the following columns: charge_id, timestamp, amount, customer_id, customer_name, invoice_id, invoice_number
Unpaid invoice report that has the following columns: invoice_id, unpaid_amount, age, invoice_number
The common part that we can see from this report is the customer_name and invoice_number column where we can get them by joining with the customers and invoices table. You can imagine it could be implemented as below if SQL was more advanced:
selectmain.*,-- if main contains customer_idcustomers.nameascustomer_name-- end-- if main contains invoice_idinvoices.numberasinvoice_number-- endfrommain-- if main contains customer_idleftjoincustomersonmain.customer_id=customers.id-- end-- if main contains invoice_idleftjoininvoicesonmain.invoice_id=invoices.id-- end
The above pattern would have been fine if SQL supported the capability to check whether a certain column exists in the previous SQL statement before writing the next SQL.
But SQL doesn’t support that!
This is why lilit-sql, the Ruby DSL for composing maintainable SQL statements, is built, and we can achieve the same thing with:
lilit-sql goes even further and supports “dynamic” columns as well where the final set of columns depends on user’s input.
This is a requirement in one of our reports called “waterfall”. For example, if a user selects the date range of June to Oct, then the columns are: person, june_amount, july_amount, aug_amount, sep_amount, and oct_amount. There’s an example of the dynamic column SQL statement in the README.md.
Since lilit-sql provides these higher-abstraction-esque capabilities (e.g. reading which column exists and defining columns dynamically), we can re-use business logic and make our engineering team more productive and efficient in maintaining a lot of SQL statements.
lilit-sql is still in its nascent. If the pain points described here resonate with you, I’d love to work with you to iterate the library to be something that fits your needs. Please don’t hesitate to reach out by creating an issue on the repository!
I’ve launched Superintendent.app 6 months ago. Initially, I built it to make me more productive at work since I work with
a lot of CSV files, and I loathe using Excel. After building it, I’ve realized that having a Desktop app that can handle GBs of
CSV files is really versatile. I can analyze CSV files from customers, cross-check (i.e. join) them with internal data, reformat them in
some ways, and etc.
Analyzing log files is another interesting use case. I’m sure there are tons of tools that can do the same thing, though I’m not sure
if those tools are backed by a real database. Superintendent.app is backed by Sqlite, a real database, which can result in a different
set of strengths and weaknesses e.g. medium initial load time (10s for 1GB file), quick query time, and powerful functionality.
To recap, Superintedent.app is an offline Desktop app that allows you to import CSV/TSV files (and several other tabular formats) and
write SQLs on those files. Here’s how the app looks like:
As a desktop app, Superintendent.app can handle GBs of files easily. Moreover, the privacy and security aspect of an offline
Desktop app yields better peace of mind.
Superintendent.app also enables you to cross-check (i.e. join) records between the log file and a database table (exported in CSV).
Since Superintendent.app is backed by Sqlite, which is powerful but still quite limited in SQL capabilities compared to other SQL-style databases),
we’ve added 3 convenient SQL functions to make our lives easier, namely, date_parse,
regex_extract, and regex_replace.
WITH sanitized AS (
SELECT
REGEX_EXTRACT(
'^(([^\s]+ ){0,8})', -- Get the first 8 words
REGEX_REPLACE('[0-9]+', REGEX_REPLACE('\s+', message, ' ', false), '0', false) -- Squeeze whitespaces and digits
) AS message,
REGEX_REPLACE('[0-9]+', REGEX_REPLACE('\s+', program, ' ', false), '0', false) AS program,
REGEX_REPLACE('[0-9]+', REGEX_REPLACE('\s+', source_name, ' ', false), '0', false) AS source_name
FROM "2021_11_24_15"
)
SELECT COUNT(*) AS occurence, message FROM sanitized GROUP BY message ORDER BY COUNT(*) DESC
The above SQL yields the result below:
I wish Sqlite supports define a user-defined function within a SQL statement,
so the code will be cleaner. But this is an improvement for another day.
Searching
Searching is the simplest one because you can use column LIKE '%something%' or regex_extract('regex', column) IS NOT NULL in order to search for a specific string.
For example, if you want to search for the log lines where write-iops is more than or equal to 0.087, you can use the SQL below:
SELECT
CAST(regex_extract('write-iops=([0-9\.]+)', message) AS decmial) AS write_iops,
regex_extract('(.{0,30}write-iops=[0-9\.]+.{0,30})', message) AS context, -- Show context
*
FROM "2021_11_24_15"
WHERE write_iops >= 0.087
The SQL yields the result as shown below:
Beautiful, isn’t it?
Wrapping up
If you are familiar with SQL, using SQL to analyze log files makes a lot of sense. You can visit https://superintendent.app to try it out.
Thank you for reading until the end. I’ll see you next time.
Since starting at a new company, I’ve been working on an accounting report that comes in the form of CSVs, so I’ve worked with a lot of CSVs.
As a person who knows SQL well but isn’t good at Excel, working with CSV files in Excel is such a nightmare.
Every time I was doing something, I felt that I would have spent 10x less time if I could use SQL.
VLOOKUP and pivot tables are absolutely difficult to use. It’s not just writing stuff. It’s a combination of coding and meddling with UI.
SQL is much more ergonomic. It’s just a piece of text. There are few benefits of being “a piece of text”:
You can save the SQL in a file and reuse it on different CSV files that have the same format.
It’s easier to check for correctness because the whole logic is contained within a single SQL.
Besides the above, there’s a blocker where Excel cannot load a CSV with more than 1M rows. Using SQL with an actual database won’t encounter this blocker.
In this blog, I’d like to show how VLOOKUP and pivot tables can be replaced with SQL, and hopefully this would inspire you to learn the basic of SQLs.
VLOOKUP
VLOOKUP is somewhat equivalent to JOIN in SQL.
Let’s say there are 2 CSV files that are associated to each other through employee_id.
employee_id
name
1
John
2
Jane
3
Mary
and
employee_id
salary
1
70000
2
80000
3
60000
We can write a SQL that will construct a table that contains name and salary on the same row:
SELECT
n.employee_id,
n.name,
s.salary
FROM names n
JOIN salaries s
ON n.employee_id = s.employee_id
The above SQL yields:
employee_id
name
salary
1
John
70000
2
Jane
80000
3
Mary
60000
You can see that it’s just a short SQL to achieve what VLOOKUP can do.
Pivot tables
Pivot tables is somewhat equivalent to GROUP BY.
Let’s say there’s one CSV that contains sales per location, and you want to get total sales per location. Here’s the CSV:
state
price
CA
10
CA
12
WA
17
WA
1
NY
9
We can write a SQL that will construct a table that group by state and compute the total sales of each state:
SELECT
state,
SUM(price) as price
FROM sales
GROUP BY state
The above SQL yields:
state
price
CA
22
WA
18
NY
9
If you are familiar with SQL, this would take less than a minute to write.
What next?
Since you read up until this point, I know the question that you are having now is: “ok, I’m convinced. But where can I write SQL on CSVs?”.
I had asked the very same question, and there are many options like:
However, I ended up building my own Desktop application for this purpose: Superintendent.app, which supports Windows, Mac, and Linux.
I intend for Superintendent.app to be an SQL-focused spreadsheet Desktop app that offers great UX like Excel does (e.g. sort columns, sum values on the selected cells).
It’s not there today, but we are moving toward that directly.
Since Superintendent.app is backed by SQLite, it is very fast (e.g. loading 1GB file within 10s on Macbook Pro 2020) and can handle a dataset as large as your memory allows.
While I want you to try Superintendent.app, the main gist is to show you how SQL can significantly improve your workflow, and hopefully you are inspired to start learning SQL.
Thank you for reading until the end, and I’ll see you next time :)
With this mechanism, Tip can be used with any Mac OS app!
Tip also only see the text when user explicitly triggers this feature by hitting the configured shortcut.
Tip gets the selected text, calls the user script, and renders the result in a tooltip as shown below:
Then, you can select an item to perform the defined action. Currently, Tip supports 2 actions: (1) copying to clipboard and (2) opening a URL, which can be used to trigger a Mac app that supports URL like opening a file in IntelliJ.
With a user script that you write yourself, this means you can customize Tip to do anything you want.
My favourite technical detail on Tip is that it runs in App Sandbox
without requesting for additional permissions. Tip can only execute (and read-only) a user script placed in ~/Library/Application\ Scripts/tanin.tip/.
Privacy-wise, this is a huge win.
I’ve been using Tip for 3-4 months now with many use cases. Surprisingly, the use case I use most frequently is:
selecting a file in an error stacktrace and opening that file in IntelliJ/Github. The user script uses the text to search through local file and offers a tooltip item to open matching files on IntelliJ/Github. It looks like below:
Even if we use a great timezone library like tzinfo, a bug might still happen. That’s exactly what happened to me.
I was building a graph whose x axis is of day frequency. Our database stored data in UTC. Since I wanted the days to be in accordance with local timezone, what I did was iterating through days’ boundaries in local time and converting each boundary to UTC time. For example:
2019-03-09 00:00:00 America/Los_Angeles -> 2019-03-09 08:00:00 UTC
2019-03-10 00:00:00 America/Los_Angeles -> 2019-03-10 08:00:00 UTC
2019-03-11 00:00:00 America/Los_Angeles -> 2019-03-11 07:00:00 UTC
Then, I use the days’ boundaries in UTC to query our database.
That code was simple. Using a timezone library helped immensely. 2019-03-10 has only 23 hours, which is correct. Beautiful.
Or so I thought.
This code was deployed, and, what a surprise, I got the error: 2019-09-08 is an invalid time. – Huh, what do you mean the time is invalid?
After digging in, I’ve found that 2019-09-08 00:00:00 doesn’t exist in Chile because Chile turns the clock forward 1 hour on 2019-09-08 00:00:00 according to their daylight saving rules; 2019-09-08 starts on 01:00:00.
We need to be aware that, (1) when we turn the clock forward, the points of time in between don’t exist, and (2) when we turn the clock backward, the points of time in between occur twice. In tzinfo (Ruby’s timezone library), converting a non-existing local time UTC raises the PeriodNotFound exception, and converting a twice-occurring local time to UTC raises the AmbiguousTime exception.
After this bug, I’ve researched a bit more and have found some unique timezones that are great to be included in tests:
America/Santiago is unique because of when its daylight saving adjustments occur. On 2019-09-08, the time jumps forward one hour when it reaches 2019-09-08 00:00:00. So, the day actually starts on 2019-09-08 01:00:00, and the time [2019-09-08 00:00:00, 2019-09-08 01:00:00) doesn’t exist. My assumption that a day starts at midnight is simply false.
Antarctica/Troll adjusts their daylight saving by 2 hours. Though the adjustment doesn’t happen on a day’s boundaries, it breaks my assumption that the daylight saving adjustment only happens by 1 hour.
Australia/Lord_Howe adjusts their daylight saving by 30 minutes between UTC+10:30 and UTC+11. It breaks my assumption that the daylight saving adjustment only happens in the hour resolution.
Australia/Adelaide is a 30-minutes timezone, and their daylight saving adjustment switches the timezone between UTC+10:30 and UTC+9:30.
Australia/Eucla is UTC+8:45, a 45-minute timezone, that doesn’t observe daylight saving.
Pacific/Samoa changed their timezones across the international dateline, so their 30 Dec 2011 doesn’t exist.
One small thing to note is, if you are designing a time-series database, using 15-minute bucket is necessary. As far as I know, all the timezones fit into the 15-minute bucket.
IANA timezone database is the official timezone database that tracks the daylight saving dates, and sometimes we forget to update our copy of the IANA timezone database. For example, for America/Santiago, timezoneconverter.com suggests that the daylight saving ends on 26 April 2020, but zeitverschiebung.net suggests 4 April 2020. I was frustratedly stuck with writing tests on the wrong date. So, be aware of this inconsistency.
Another thing to remember is that the daylight saving’s starting and ending dates are somewhat arbitrary. The dates are set by government and can be changed for varieties of reasons. For example, on 4 Oct 2018, Brazil’s government announced that they would move the daylight saving’s start date from 4 Nov 2018 to 18 Nov 2018 to accommodate a nation-wide university exam. Two weeks later, Brazil’s government reverted that change. So, it’s important to update our copy of IANA timezone database frequently.
The lesson here is that, even if you use a timezone library, you still need to test against these unique timezones to make sure your code work correctly.
I’m sure I’m still missing a bunch of odd cases around timezones. What’s your horror story on working with timezones? What interesting timezones should we add to our tests? Share your stories on Reddit.
I started taking notes in workplace a few years back. What nudged me to start was that I occasionally forgot about my action items after a meeting; I felt forgetful. Since then, I’ve gone through different processes of note taking, and today I want to share what my current process looks like.
I carry a small physical notebook with me everywhere. There are 3 categories that I always note: (1) to-do items, (2) questions, and (3) notable info. Then, later on, I transfer these notes into a digital form.
Noting to-do items is the most important action. When someone asks me to do something, I’ll never want to forget about it. Noting the deadline is as important. I write it down.
Noting questions makes me more informed. If a question pops up in my mind, I note it immediately. No matter how dumb the question seems. Don’t even think about answering it. I write it down.
Though notable info sounds vague, I have a low threshold for it. If something surprises me or I didn’t know about it before, I write it down.
Here you might notice the theme, “just write it down”. I write it down without thinking. When I write down a to-do item, I don’t think about what it entails. When I write down a question, I don’t think about the answer. When I write down notable info, I don’t label it as good or bad. I want to avoid developing impression prematurely. Writing it down without judgement frees my mind from the worry (that I might forget) and allows me to focus on the conversation at hand.
One skill developed with the above process is “Suspended judgement”, the ability to acknowledge without judging. It’s something I’m looking forward to getting better at over time.
When I have free time, I would go through my notes. One by one, I would think about it deeply, transfer the item to a digital form, and cross it out. This is a good opportunity to spend time researching, thinking, and elaborating on each noted item.
The transferred items are crossed with the red ink.
Transferring notes from its physical form to the digital form is an extremely beneficial action. It forces me to review the notes; this yields one more occurrence of Spaced Repetition, which helps me retain important info. The solitude while transferring notes also allows me time to think more deeply about what I noted.
My note taking setup is rather simple. I use a mini Rocketbook with a 4-color Frixion pen. With this combination, I can reuse the notebook forever. I note in these 3 style of bullet points: · (dot) for tasks, ? (question mark) for question, and - (dash) for notable info. On the digital side, I store notes in plaintext and sync to Github using my homegrown app, Git Notes.
Taking notes is a very personal process. What works for me might not works for you. You’ll need to iterate at your own pace to figure out what you like and don’t like. My general advice is to approach it casually. Don’t stretch yourself too much. This is similar to practicing meditation, yoga, or anything alike. We want to deepen our practices, but we only move forward when we are comfortable.
Once we have this noting-thinking loop built into our daily routine, enriching our note taking practice becomes much easier. We can become more considerate by noting other people’s states of minds and later thinking about how that impacts what you do. We can become more aware of our mistakes by noting and later thinking about how to avoid them next time. The possibilities are endless.
It has been a few years already since I started taking note. I feel more grounded. I feel more thoughtful. I feel more confident retaining the info flowing through me. So, I encourage you to start taking notes in workplace, and I hope my process serves as one example that you can take and personalize to suit your style.
Yesterday I’ve spent 5 hours debugging a strange issue when using the Algolia’s Scala client with Playframework during local development. That is, the period where auto-reloading happens when code is changed.
For a long time (~9 months), we’ve experienced a DNS error “flakily”; later, it turned out to be a non-flaky issue. But more on that later.
The error is an injection error. We can’t initialize AlgoliaClient because we can’t initialize DnsNameResolver.
Caused by: java.lang.NullPointerException
at io.netty.resolver.dns.DnsNameResolver.<init>(DnsNameResolver.java:303)
at io.netty.resolver.dns.DnsNameResolverBuilder.build(DnsNameResolverBuilder.java:379)
at algolia.AlgoliaHttpClient.<init>(AlgoliaHttpClient.scala:56)
at algolia.AlgoliaClient.<init>(AlgoliaClient.scala:64)
...
Please note that, for a newer version of Netty, the error will be around FailedChannel cannot be casted to Channel. But it still occurs at the same place.
Well, ok, it was a network thingy. It could be flaky, so I ignored it for several months.
Yesterday, I’ve found out that this injection error happens almost exactly when Playframework reloaded code around 23 times. It took me a lot of times to test this hypothesis because changing code 23 times was tedious.
I wrote a script that instantiating AlgoliaClient multiple times, and the exception was always raised at the 26rd AlgoliaClient. The exception wasn’t exactly helpful though. I did a lot of random things afterward with no progress for another hour.
What helped me progress was that I tried to instantiate the 27st AlgoliaClient, and there it was. The actual exception showed itself:
Caused by: java.net.SocketException: maximum number of DatagramSockets reached
at sun.net.ResourceManager.beforeUdpCreate(ResourceManager.java:72)
at java.net.AbstractPlainDatagramSocketImpl.create(AbstractPlainDatagramSocketImpl.java:69)
at java.net.TwoStacksPlainDatagramSocketImpl.create(TwoStacksPlainDatagramSocketImpl.java:70)
After googling for ResourceManager, it turned out that the limit to the number of datagram sockets was 25!
I tested this hypothesis by running sbt Dsun.net.maxDatagramSockets=4 'runMain TheScript', and the script failed after the 4st AlgoliaClient.
Now I know that the instantiated AlgoliaClient wasn’t cleaned up properly, so I simply needed to close it.
Unfortunately, algoliaClient.close()doesn’t close its DNS name resolver. Fortunately, the DNS name resolver is public, and I can close it myself with algoliaClient.httpClient.dnsNameResolver.close().
Now I knew I needed to clean up AlgoliaClient before Playframework reloads code. And, fortunately, Playframework offers a stop hook that is invoked before code reloading.
Here are some lessons for me:
I should have wrote the script much earlier. Changing code 25 times was extremely tedious and time-wasting. Reproducing a bug easily would have saved me a lot of time.
Biased to make class’s members public. It’ll be helpful for future users. Scala gets it right to default everything to public, while Java defaults everything to somewhat private. Making a member private should be a conscious and thoughtful decision. If DNS name resolver wasn’t public, it would be tricky for me to progress.
I should have not ignored the injection error during local development, no matter how flaky it is.
In my previous post on parallelising tests in SBT, it doesn’t work well in practice or, at least, on CircleCI. The main disadvantage is that it doesn’t balance the tests by their run time. Balancing tests by their run time would reduce the total time significantly.
CircleCI offers the command-line tool, named circleci, obviously, for splitting tests. One mode of splitting is splitting tests based on how long individual tests take. If scala_test_classnames contains a list of test classes, we can split using circleci tests split --split-by=timings --timings-type=classname scala_test_classnames. The output is a list of test classes that should be run according to the machine numbered CIRCLE_NODE_INDEX out of the CIRCLE_NODE_TOTAL machines. circleci conveniently reads these two env variables automatically.
At a high level, we want sbt to print out all the test classes. We feed those classes to circleci. Then, we feed the output of circleci to sbt testOnly. Finally, we use CircleCI’s store_test_results to store the test results which includes time. circleci uses this info to split tests accordingly in subsequential runs.
Now it’s time to write an SBT task again. This task is straigtforward because, when googling “sbt list all tests”, the answer is one of the first items. Here’s how I do it in my codebase:
val printTests = taskKey[Unit]("Print full class names of tests to the file `test-full-class-names.log`.")
printTests := {
import java.io._
println("Print full class names of tests to the file `test-full-class-names.log`.")
val pw = new PrintWriter(new File("test-full-class-names.log" ))
(definedTests in Test).value.sortBy(_.name).foreach { t =>
pw.println(t.name)
}
pw.close()
}
Then, in .circleci/config.yml, we can use the below commands:
SBT, the official build tool for Scala, is a very complex build tool. It’s one of those things that makes me wonder if I am stupid or the tool’s complexity surpasses average human intelligence.
I’ve done a few things with SBT (e.g. printing the list of all tests), usually using the trial-and-error approach, and this time I want to add test parallelism to my project.
The requirement is straightforward. Heroku CI or CircleCI can run our tests on multiple machines. In each machine, 2 environment variables, say, MACHINE_INDEX and MACHINE_NUM_TOTAL, are set. We can use these 2 environment variables to shard our tests.
I’ve been thinking about my English pronunciation issue since I’ve moved to Bay Area (from Thailand) in 2012. I can’t seem to speak English properly.
There were many moments that made me feel bad about myself.
I can’t pronounce the word guitar. I don’t get it, and people don’t understand me when I pronounce this word. I once said guitar to a lady at the post office. She didn’t get it. I pronounced to her like several times in different stress patterns. She was very patient… but she still didn’t get. Eventually, I spelled it out for her. She got it and pronounced guitar back to me. I felt like that was exactly how I pronounced it.
