I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL. However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'll put tog…

The first time I used a BI tool, I was confused by the difference between "dimensions" and "measures. I will try to briefly explain the differences and ideas here. name In Tableau, they are called "dimensions" and "measures". In PowerBI, t…

This is a memo on how to use Microsoft SQL Server (MSSQL) with JavaScript. The library is tedious, which is also recommended by Microsoft. tedios is a callback style library, but it is written in async, await, and TypeScript in a modern st…

To the UI for specifying the display range of time series graphs The default is to show the most recent X days, and you can specify any date in the calendar when you need it." I often see things like this. But I can't do the equivalent wit…

I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL. However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'm going to…

I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL. However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'll put tog…

I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL. However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'll put tog…

I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL. However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'll put tog…

I usually use SQL, but when I am working with array data in JavaScript, I sometimes wish I could write queries in SQL for JavaScript arrays. On the other hand, C# has a language called LINQ that can process arrays with SQL-like queries. Th…

When I started learning SQL, the window functions (analytic functions) were the most difficult to understand. Although you do not need to use the window function when using a database simply as a place to store and retrieve data, it is a v…

I have not used Redshift for the past few years, having migrated from AWS Redshift to Google BigQuery. However, Redshift Serverless, which is a pay-as-you-go Redshift, finally appeared and I started to get curious about it. Sure enough, I …

Since Google BigQuery is pay-as-you-go and relatively inexpensive, I had been using it without much concern about the fees. Big data analysis was a breakthrough in that matters that until now could only be guessed at could actually be expr…

I had the opportunity to take the Google Professional Data Engineer certification exam and I passed! There is not much information about Google Professional Data Engineer certification, so I will try to write a success story for those who …

This is a memo on how to use SQLite with JavaScript. We did not elaborate too much, but focused on the minimum necessary functions. I will not go into the usage of SQLite itself, but please refer to this way (direction close to the speaker…

PS Now there is another, better way, which is shown below. www.ekwbtblog.com To the UI for specifying the display range of time series graphs The default is to show the most recent X days, and you can specify any date in the calendar when …

Tableau is intuitive, and you can create graphs by dragging and dropping with the mouse. However, when trying to port Excel graphs to Tableau, sometimes it is not possible to create a graph properly using only mouse operations. Such graphs…

I was interested in single sign-on (SSO) services and tried various services for a while. www.ekwbtblog.com I wanted to log in with my Office365 user as a key, so I ended up using Azure Active Directory SSO. As for the usage, I mainly use …

When loading Google Cloud Storage files into Google BigQuery, multiple files can be loaded at once. However, I got stuck trying to do that with Google BigQuery's Node.js SDK, so here are my notes on that. Overview and Issues Load using tab…

We had the opportunity to collect data using the Twitter API. The Twitter API has a Search API for searching tweets, just like Google search, which I used this time. I usually use Twitter mainly as a reader, so I was not really aware of it…

I tried to get data from Twitter using the Twitter API to see if I could use it to learn about trends in the world. As it turns out, I had heard rumors that the Twitter API was too restrictive, but it was really too tight to be useful... S…

Tableau's default graphs are quite beautiful. For practical use, it is sufficient to slightly modify the defaults, but for graphs used for documentation purposes, you may want to change the appearance of the graphs in various ways to match…

SQL Server data is read by BI tools to create graphs. In that case, we did not want to use a user with write permission because the data would only be read and various people would create graphs, so we created a read-only user for sharing.…

I did not know that SQL Server was the first time to use Azure SQL Database, but in SQL Server, queries across databases can be easily written by specifying the database, as shown below. SELECT * FROM [<db_name>].dbo.test_table However, when I try </db_name>…

It is difficult to arrange graphs in a Tableau dashboard because it is hard to get the layout to look the way I want. The reason is that when you drop or move an object with the mouse, it does not enter the intended hierarchy, Because chan…

To aggregate data by month/week, first find the beginning date of the month/week of the date and GROUP by that beginning date. If the DATE_TRUNC() function can be used to get the head date, such as Google BigQuery, you can aggregate the da…

For example, "In a user's event log, I want to extract the last event the user performed." Sometimes you want to retrieve the last record of data divided by groups. The window function can be used to obtain the following WITH tbl AS ( SELE…

Since Spark is a Python program, it can be written quite freely. However, since I always have a general idea of what I need to do, and knowing various ways of writing Spark makes it harder to remember, I will summarize my personal frequent…

I have tried AWS Glue before and here are my thoughts on it. AWS Glue is made of Apache Spark, and it was interesting for me to touch Spark for the first time at that time, I was thinking that I would put together a usage guide for Glue wh…

How to access AWS S3 from Spark (Google Dataproc). procedure Spark Configuration The following Spark and Haddop settings will allow you to read and write AWS S3 files from Spark. Load the following AWS-related jar files into Spark aws-java…

How to access Microsoft SQL Server (Azure Database) from Spark (Google Dataproc). procedure Spark Configuration The following Spark settings will allow you to read and write SQL Server data from Spark. Download the MS SQL Server JDBC jar f…