These are some of the most common aggregate SQL functions. You saw them in the previous section. You can use these functions to calculate average, maximum, minimum, and total of numeric column values on one or more tables. The COUNT function returns the number of rows that match the SQL statement criteria. Note that after loading all brainwave readings into an Azure SQL database, executing the following function resulted in 459,825 rows:
SELECT COUNT(*) FROM READING
The code to upload the JSON readings is located on GitHub at https://github.com/benperk/ADE in the Chapter02/Source Code/brainjammer‐cosmos directory. The actual brainwave reading data is located in the BrainwaveData directory. Finally, these are some basic aggregate functions you likely already know pretty well. Since the examples in the previous section covered these aggregate functions, no further explanation is necessary.
BULK INSERT
This can be used to load data from a file existing on an Azure Data Lake Storage container. You first need to create an external data source, using the following syntax:
CREATE EXTERNAL DATA SOURCE PlayingGuitarPOWWITH ( TYPE = BLOB_STORAGE,LOCATION = ‘https://<account>.blob.core.windows.net/<container>/<path>’);
Then identify the specific file and the table into which the data should be loaded:
BULK INSERT [dbo].[READINGCSV]FROM ‘csharpguitar-brainjammer-pow-PlayingGuitar-0911.csv’
WITH (DATA_SOURCE = ‘PlayingGuitarPOW’,FIRSTROW = 2,FIELDTERMINATOR = ‘,’,ROWTERMINATOR = ‘0x0a’);
The columns in the CSV file must match the table that will receive the data contained in the file. You might encounter this command more in the context of Azure SQL. In the Azure Synapse Analytics SQL pool scenario, it is more common to use the COPY TO command, which is explained later.
CASE
In the “Data Programming” section earlier, you learned about the PySpark when() method. A SQL CASE command results in the same outcome:
SELECT SESSION_ID, ELECTRODE = CASE ELECTRODE_ID WHEN 1 THEN ‘AF3’ WHEN 2 THEN ‘AF4’ WHEN 3 THEN ‘T7’ WHEN 4 THEN ‘T8’WHEN 5 THEN ‘Pz’ELSE ‘Invalid’ END, Value FROM [dbo].[READING]ORDER BY READING_DATETIME
In the READING table ELECTRODE_ID is an integer. This statement would make the output more user friendly by converting those integers to the name of the electrode.
COALESCE
This SQL function is helpful in managing data columns that may contain a NULL value. Instead of returning NULL or an empty string, you can use COALESCE to perform some evaluations and return an alternative value. The following snippet will evaluate the content of the VALUE column. If the content is NULL, then it returns 0; if not, the command returns the value stored on the database.
COALESCE(VALUE, 0)
You will find this function typically embedded within a SELECT statement, as follows:
SELECT READING_DATETIME, ELECTRODE_ID, FREQUENCY_ID, COALESCE(VALUE, 0)
FROM READING
