GoLang – Consuming Oracle REST API from an Oracle Cloud Database)

Posted on Updated on

Does anyone write code to access data in a database anymore, and by code I mean SQL?  The answer to this question is ‘It Depends’, just like everything in IT.

Using REST APIs is very common for accessing processing data with a Database. From using an API to retrieve data, to using a slightly different API to insert data, and using other typical REST functions to perform your typical CRUD operations. Using REST APIs allows developers to focus on write efficient applications in a particular application, instead of having to swap between their programming language and SQL. In later cases most developers are not expert SQL developer or know how to work efficiently with the data. Therefore leave the SQL and procedural coding to those who are good at that, and then expose the data and their code via REST APIs. The end result is efficient SQL and Database coding, and efficient application coding. This is a win-win for everyone.

I’ve written before about creating REST APIs in an Oracle Cloud Database (DBaaS and Autonomous). In these writings I’ve shown how to use the in-database machine learning features and to use REST APIs to create an interface to the Machine Learning models. These models can be used to to score new data, making a machine learning prediction. The data being used for the prediction doesn’t have to exist in the database, instead the database is being used as a machine learning scoring engine, accessed using a REST API.

Check out an article I wrote about this and creating a REST API for an in-database machine learning model, for Oracle Magazine.

In that article I showed how easy it was to use the in-database machine model using Python.

Python has a huge fan and user base, but some of the challenges with Python is with performance, as it is an interrupted language. Don’t get be wrong on this, as lots of work has gone into making Python more efficient. But in some scenarios it just isn’t fast enough. In does scenarios people will switch into using other quicker to execute languages such as C, C++, Java and GoLang.

Here is the GoLang code to call the in-database machine learning model and process the returned data.

import (
    "bytes"
    "encoding/json"
    "fmt"
    "io/ioutil"
    "net/http"
    "os"
)

func main() {
    fmt.Println("---------------------------------------------------")
    fmt.Println("Starting Demo - Calling Oracle in-database ML Model")
    fmt.Println("")

    // Define variables for REST API and parameter for first prediction
    rest_api = "<full REST API>"

    // This wine is Bad
    a_country := "Portugal"
    a_province := "Douro"
    a_variety := "Portuguese Red"
    a_price := "30"

    // call the REST API adding in the parameters
    response, err := http.Get(rest_api +"/"+ a_country +"/"+ a_province +"/"+ a_variety +"/"+ a_price)
    if err != nil {
        // an error has occurred. Exit
        fmt.Printf("The HTTP request failed with error :: %s\n", err)
        os.Exit(1)
    } else {
        // we got data! Now extract it and print to screen
        responseData, _ := ioutil.ReadAll(response.Body)
        fmt.Println(string(responseData))
    }
    response.Body.Close()

    // Lets do call it again with a different set of parameters

    // This wine is Good - same details except the price is different
    a_price := "31"

    // call the REST API adding in the parameters
    response, err := http.Get(rest_api +"/"+ a_country +"/"+ a_province +"/"+ a_variety +"/"+ a_price)
    if err != nil {
        // an error has occurred. Exit
        fmt.Printf("The HTTP request failed with error :: %s\n", err)
        os.Exit(1)
    } else {
        responseData, _ := ioutil.ReadAll(response.Body)
        fmt.Println(string(responseData))
    }
    defer response.Body.Close()

    // All done! 
    fmt.Println("")
    fmt.Println("...Finished Demo ...")
    fmt.Println("---------------------------------------------------")
}

 

XGBoost in Oracle 20c

Posted on Updated on

Another of the new machine learning algorithms in Oracle 20c Database is called XGBoost. Most people will have come across this algorithm due to its recent popularity with winners of Kaggle competitions and other similar events.

XGBoost is an open source software library providing a gradient boosting framework in most of the commonly used data science, machine learning and software development languages. It has it’s origins back in 2014, but the first official academic publication on the algorithm was published in 2016 by Tianqi Chen and Carlos Guestrin, from the University of Washington.

The algorithm builds upon the previous work on Decision Trees, Bagging, Random Forest, Boosting and Gradient Boosting. The benefits of using these various approaches are well know, researched, developed and proven over many years. XGBoost can be used for the typical use cases of Classification including classification, regression and ranking problems. Check out the original research paper for more details of the inner workings of the algorithm.

Regular machine learning models, like Decision Trees, simply train a single model using a training data set, and only this model is used for predictions. Although a Decision Tree is very simple to create (and very very quick to do so) its predictive power may not be as good as most other algorithms, despite providing model explainability. To overcome this limitation ensemble approaches can be used to create multiple Decision Trees and combines these for predictive purposes. Bagging is an approach where the predictions from multiple DT models are combined using majority voting. Building upon the bagging approach Random Forest uses different subsets of features and subsets of the training data, combining these in different ways to create a collection of DT models and presented as one model to the user. Boosting takes a more iterative approach to refining the models by building sequential models with each subsequent model is focused on minimizing the errors of the previous model. Gradient Boosting uses gradient descent algorithm to minimize errors in subsequent models. Finally with XGBoost builds upon these previous steps enabling parallel processing, tree pruning, missing data treatment, regularization and better cache, memory and hardware optimization. It’s commonly referred to as gradient boosting on steroids.

The following three images illustrates the differences between Decision Trees, Random Forest and XGBoost.

The XGBoost algorithm in Oracle 20c has over 40 different parameter settings, and with most scenarios the default settings with be fine for most scenarios. Only after creating a baseline model with the details will you look to explore making changes to these. Some of the typical settings include:

  • Booster =  gbtree
  • #rounds for boosting = 10
  • max_depth = 6
  • num_parallel_tree = 1
  • eval_metric = Classification error rate  or  RMSE for regression

 

As with most of the Oracle in-database machine learning algorithms, the setup and defining the parameters is really simple. Here is an example of minimum of parameter settings that needs to be defined.

BEGIN
   -- delete previous setttings
   DELETE FROM banking_xgb_settings;

   INSERT INTO BANKING_XGB_SETTINGS (setting_name, setting_value)
   VALUES (dbms_data_mining.algo_name, dbms_data_mining.algo_xgboost);

   -- For 0/1 target, choose binary:logistic as the objective.
   INSERT INTO BANKING_XGB_SETTINGS (setting_name, setting_value)
   VALUES (dbms_data_mining.xgboost_objective, 'binary:logistic);

   commit;
END;

 

To create an XGBoost model run the following.


BEGIN
   DBMS_DATA_MINING.CREATE_MODEL (
      model_name          => 'BANKING_XGB_MODEL',
      mining_function     => dbms_data_mining.classification,
      data_table_name     => 'BANKING_72K',
      case_id_column_name => 'ID',
      target_column_name  => 'TARGET',
      settings_table_name => 'BANKING_XGB_SETTINGS');
END;

That’s all nice and simple, as it should be, and the new model can be called in the same manner as any of the other in-database machine learning models using functions like PREDICTION, PREDICTION_PROBABILITY, etc.

One of the interesting things I found when experimenting with XGBoost was the time it took to create the completed model. Using the default settings the following table gives the time taken, in seconds to create the model.

As you can see it is VERY quick even for large data sets and gives greater predictive accuracy.

 

MSET (Multivariate State Estimation Technique) in Oracle 20c

Posted on

Oracle 20c Database comes with some new in-database Machine Learning algorithms.

The short name for one of these is called MSET or Multivariate State Estimation Technique. That’s the simple short name. The more complete name is Multivariate State Estimation Technique – Sequential Probability Ratio Test.  That is a long name, and the reason is it consists of two algorithms. The first part looks at creating a model of the training data, and the second part looks at how new data is statistical different to the training data.

 

What are the use cases for this algorithm?  This algorithm can be used for anomaly detection.

Anomaly Detection, using algorithms, is able identifying unexpected items or events in data that differ to the norm. It can be easy to perform some simple calculations and graphics to examine and present data to see if there are any patterns in the data set. When the data sets grow it is difficult for humans to identify anomalies and we need the help of algorithms.

The images shown here are easy to analyze to spot the anomalies and it can be relatively easy to build some automated processing to identify these. Most of these solutions can be considered AI (Artificial Intelligence) solutions as they mimic human behaviors to identify the anomalies, and these example don’t need deep learning, neural networks or anything like that.

Other types of anomalies can be easily spotted in charts or graphics, such as the chart below.

There are many different algorithms available for anomaly detection, and the Oracle Database already has an algorithm called the One-Class Support Vector Machine. This is a variant of the main Support Vector Machine (SVD) algorithm, which maps or transforms the data, using a Kernel function, into space such that the data belonging to the class values are transformed by different amounts. This creates a Hyperplane between the mapped/transformed values and hopefully gives a large margin between the mapped/transformed points. This is what makes SVD very accurate, although it does have some scaling limitations. For a One-Class SVD, a similar process is followed. The aim is for anomalous data to be mapped differently to common or non-anomalous data, as shown in the following diagram.

 

Getting back to the MSET algorithm. Remember it is a 2-part algorithm abbreviated to MSET. The first part is a non-linear, nonparametric anomaly detection algorithm that calibrates the expected behavior of a system based on historical data from the normal sequence of monitored signals. Using data in time series format (DATE, Value) the training data set contains data consisting of “normal” behavior of the data. The algorithm creates a model to represent this “normal”/stationary data/behavior. The second part of the algorithm compares new or live data and calculates the differences between the estimated and actual signal values (residuals). It uses Sequential Probability Ratio Test (SPRT) calculations to determine whether any of the signals have become degraded. As you can imagine the creation of the training data set is vital and may consist of many iterations before determining the optimal training data set to use.

MSET has its origins in computer hardware failures monitoring. Sun Microsystems have been were using it back in the late 1990’s-early 2000’s to monitor and detect for component failures in their servers. Since then MSET has been widely used in power generation plants, airplanes, space travel, Disney uses it for equipment failures, and in more recent times has been extensively used in IOT environments with the anomaly detection focused on signal anomalies.

How does MSET work in Oracle 20c?

An important point to note before we start is, you can use MSET on your typical business data and other data stored in the database. It isn’t just for sensor, IOT, etc data mentioned above and can be used in many different business scenarios.

The first step you need to do is to create the time series data. This can be easily done using a view, but a Very important component is the Time attribute needs to be a DATE format. Additional attributes can be numeric data and these will be used as input to the algorithm for model creation.

-- Create training data set for MSET
CREATE OR REPLACE VIEW mset_train_data
AS SELECT time_id, 
          sum(quantity_sold) quantity,
          sum(amount_sold) amount 
FROM (SELECT * FROM sh.sales WHERE time_id <= '30-DEC-99’)
GROUP BY time_id 
ORDER BY time_id;

The example code above uses the SH schema data, and aggregates the data based on the TIME_ID attribute. This attribute is a DATE data type. The second import part of preparing and formatting the data is Ordering of the data. The ORDER BY is necessary to ensure the data is fed into or processed by the algorithm in the correct time series order.

The next step involves defining the parameters/hyper-parameters for the algorithm. All algorithms come with a set of default values, and in most cases these are suffice for your needs. In that case, you only need to define the Algorithm Name and to turn on Automatic Data Preparation. The following example illustrates this and also includes examples of setting some of the typical parameters for the algorithm.

BEGIN
  DELETE FROM mset_settings;

  -- Select MSET-SPRT as the algorithm
  INSERT  INTO mset_sh_settings (setting_name, setting_value)
  VALUES(dbms_data_mining.algo_name, dbms_data_mining.algo_mset_sprt);

  -- Turn on automatic data preparation
  INSERT INTO mset_sh_settings (setting_name, setting_value)
  VALUES(dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);

  -- Set alert count
  INSERT INTO mset_sh_settings (setting_name, setting_value)
  VALUES(dbms_data_mining.MSET_ALERT_COUNT, 3);

  -- Set alert window
  INSERT INTO mset_sh_settings (setting_name, setting_value)
  VALUES(dbms_data_mining.MSET_ALERT_WINDOW, 5);

  -- Set alpha
  INSERT INTO mset_sh_settings (setting_name, setting_value)
  VALUES(dbms_data_mining.MSET_ALPHA_PROB, 0.1);

  COMMIT;
END;

To create the MSET model using the MST_TRAIN_DATA view created above, we can run:

BEGIN
--   DBMS_DATA_MINING.DROP_MODEL(MSET_MODEL');

   DBMS_DATA_MINING.CREATE_MODEL (
      model_name          => 'MSET_MODEL',
      mining_function     => dbms_data_mining.classification,
      data_table_name     => 'MSET_TRAIN_DATA',
      case_id_column_name => 'TIME_ID',
      target_column_name  => '',
      settings_table_name => 'MSET_SETTINGS');
END;

The SELECT statement below is an example of how to call and run the MSET model to label the data to find anomalies. The PREDICTION function will return a values of 0 (zero) or 1 (one) to indicate the predicted values. If the predicted values is 0 (zero) the MSET model has predicted the input record to be anomalous, where as a predicted values of 1 (one) indicates the value is typical. This can be used to filter out the records/data you will want to investigate in more detail.

-- display all dates with Anomalies
SELECT time_id, pred
FROM (SELECT time_id, prediction(mset_sh_model using *) over (ORDER BY time_id) pred 
      FROM mset_test_data)
WHERE pred = 0;

Benchmarking calling Oracle Machine Learning using REST

Posted on Updated on

Over the past year I’ve been presenting, blogging and sharing my experiences of using REST to expose Oracle Machine Learning models to developers in other languages, for example Python.

One of the questions I’ve been asked is, Does it scale?

Although I’ve used it in several projects to great success, there are no figures I can report publicly on how many REST API calls can be serviced 😦

But this can be easily done, and the results below are based on using and Oracle Autonomous Data Warehouse (ADW) on the Oracle Always Free.

The machine learning model is built on a Wine reviews data set, using Oracle Machine Learning Notebook as my tool to write some SQL and PL/SQL to build out a model to predict Good or Bad wines, based on the Prices and other characteristics of the wine. A REST API was built using this model to allow for a developer to pass in wine descriptors and returns two values to indicate if it would be a Good or Bad wine and the probability of this prediction.

No data is stored in the database. I only use the machine learning model to make the prediction

I built out the REST API using APEX, and here is a screenshot of the GET API setup.

Here is an example of some Python code to call the machine learning model to make a prediction.

import json
import requests

country = 'Portugal'
province = 'Douro'
variety = 'Portuguese Red'
price = '30'

resp = requests.get('https://jggnlb6iptk8gum-adw2.adb.us-ashburn-1.oraclecloudapps.com/ords/oml_user/wine/wine_pred/'+country+'/'+province+'/'+'variety'+'/'+price)
json_data = resp.json()
print (json.dumps(json_data, indent=2))

—–

{
  "pred_wine": "LT_90_POINTS",
  "prob_wine": 0.6844716987704507
}

But does this scale, as in how many concurrent users and REST API calls can it handle at the same time.

To test this I multi-threaded processes in Python to call a Python function to call the API, while ensuring a range of values are used for the input parameters. Some additional information for my tests.

  • Each function call included two REST API calls
  • Test effect of creating X processes, at same time
  • Test effect of creating X processes in batches of Y agents
  • Then, the above, with function having one REST API call and also having two REST API calls, to compare timings
  • Test in range of parallel process from 10 to 1,000 (generating up to 2,000 REST API calls at a time)

Some of the results. The table shows the time(*) in seconds to complete the number of processes grouped into batches (agents). My laptop was the limiting factor in these tests. It wasn’t able to test when the number of parallel processes when above 500. That is why I broke them into batches consisting of X agents

* this is the total time to run all the Python code, including the time taken to create each process.

Some observations:

  • Time taken to complete each function/process was between 0.45 seconds and 1.65 seconds, for two API calls.
  • When only one API call, time to complete each function/process was between 0.32 seconds and 1.21 seconds
  • Average time for each function/process was 0.64 seconds for one API functions/processes, and 0.86 for two API calls in function/process
  • Table above illustrates the overhead associated with setting up, calling, and managing these processes

As you can see, even with the limitations of my laptop, using an Oracle Database, in-database machine learning and REST can be used to create a Micro-Service type machine learning scoring engine. Based on these numbers, this machine learning micro-service would be able to handle and process a large number of machine learning scoring in Real-Time, and these numbers would be well within the maximum number of such calls in most applications. I’m sure I could process more parallel processes if I deployed on a different machine to my laptop and maybe used a number of different machines at the same time

How many applications within you enterprise needs to process move than 6,000 real-time machine learning scoring per minute?  This shows us the Oracle Always Free offering is capable and suitable for most applications.

Now, if you are processing more than those numbers per minutes then perhaps you need to move onto the paid options.

What next? I’ll spin up two VMs on Oracle Always Free, install Python, copy code into these VMs and have then run in parallel 🙂

 

Irish Whiskey Distilleries Data Set

Posted on Updated on

I’ve been building some Irish Whiskey data sets, and the first of these data sets contains details of all the Whiskey Distilleries in Ireland. This page contains the following:

  • Table describing the attributes/features of the data set
  • Data set, in a scroll able region
  • Download data set in different formats
  • Map of the Distilleries
  • Subscribe to Twitter List containing these Distilleries, and some Twitter Hash Tags
  • How to send me updates, corrections and details of Distilleries I’ve missed

If you use this data set (and my other data sets) make sure to add a reference back to data set webpage. Let me know if you use the data set is an interesting way, share the details with me and I’ll share it on my blog and social media for you.

This data set will have it’s own Irish Distilleries webpage and all updates to the data set and other information will be made there. Check out that webpage for the latest version of things.

Data Set Description

Data set contains 45 Distilleries.

ATTRIBUTE NAME DESCRIPTION
Distillery Name of the Distillery
County County / Area where distillery is located
Address Full address of the distillery
EIRCODE EirCode for distillery in Ireland. Distilleries in Northern Ireland will not have an EIRCODE
NI_Postcode Post code of distilleries located in Northern Ireland
Tours Does the distillery offer tours to visitors (Yes/No)
Web_Site Web site address
Twitter The twitter name of the distillery
Lat Latitude for the distillery
Long Longitude for the distillery
Notes_Parent_Company Contains other descriptive information about the distillery, founded by, parent company, etc.

Data Set (scroll able region)

Data set contains 45 Distilleries.

DISTILLERY COUNTY ADDRESS EIRCODE NI_POSTCODE TOURS WEB_SITE TWITTER LAT LONG NOTES_PARENT_COMPANY
Ballykeefe Distillery Kilkenny Kyle, Ballykeefe, Cuffsgrange, County Kilkenny, R95 NR50, Ireland R95 NR50 Yes https://ballykeefedistillery.ie  @BallykeefeD 52.602034 -7.375774 Ging Family
Belfast Distillery Antrim Crumlin Road Goal, Crumlin Road, Belfast, BT14 6ST, United Kingdom BT14 6ST No http://www.belfastdistillery.com  @BDCIreland 54.609718 -5.941994 J&J McConnell
Blacks Distillery Cork Farm Lane, Kinsale, Co. Cork P17 XW70 No https://www.blacksbrewery.com  @BlacksBrewery 51.710969 -8.515579
Blackwater Waterford Church Road, Ballinlevane East, Ballyduff, Co. Waterford, P51 C5C6 P51 C5C6 No https://blackwaterdistillery.ie/  @BlackDistillery 52.147581 -8.052973
Boann Louth Lagavooren, Platin Rd., Drogheda, Co. Louth, A92 X593 A92 X593 Yes http://boanndistillery.ie/  @Boanndistillery 53.69459 -6.366558 Cooney Family
Bow Street Dublin Bow St, Smithfield Village, Dublin 7 D07 N9VH Yes https://www.jamesonwhiskey.com/en-IE/visit-us/jameson-distillery-bow-st  @jamesonireland 53.348415 -6.277266 Pernod Ricard
Bushmills Distillery Antrim 2 Distillery Rd, Bushmills BT57 8XH, United Kingdom BT57 8XH Yes https://bushmills.com  @BushmillsGlobal 55.202936 -6.517221
Cape Clear Cork Cape Clear Island, Knockannamaurnagh, Skibbereen, Co. Cork P81 RX70 No https://www.capecleardistillery.com/  @capedistillery 51.4509 -9.483047
Clonakilty Cork The Waterfront, Clonakilty, Co. Cork P85 EW82 Yes https://www.clonakiltydistillery.ie/  @clondistillery 51.62165 -8.8855 Scully Family
Connacht Whiskey Distillery Mayo Belleek, Ballina, Co Mayo, F26 P932 F26 P932 Yes https://connachtwhiskey.com  @connachtwhiskey 54.122131 -9.143779
Cooley Distillery Louth Dundalk Rd, Maddox Garden, Carlingford, Dundalk, Co. Louth A91 FX98 Yes 53.996544 -6.221563 Beam Suntory
Copeland Distillery Down 43 Manor Street, Donaghadee, Co Down, Northern Ireland, BT21 0HG BT21 0HG Yes https://copelanddistillery.com @CopelandDistill 54.642699 -5.532739
Dingle Distillery Kerry Farranredmond, DIngle, Co. Kerry V92 E7YD Yes https://dingledistillery.ie/  @DingleWhiskey 52.141928 -10.289287
Dublin Liberties Dublin 33 Mill Street, Dublin 8, D08 V221 D08 V221 Yes https://thedld.com  @WeAreTheDLD 53.337343 -6.276367
Echlinville Distillery Down 62 Gransha Rd, Kircubbin, Newtownards BT22 1AJ, United Kingdom BT22 1AJ Yes https://echlinville.com/  @Echlinville 54.46909 -5.509397
Glendalough Wicklow Unit 9 Newtown Business And Enterprise Centre, Newtown Mount Kennedy, Co. Wicklow, A63 A439 A63 A439 No https://www.glendaloughdistillery.com/  @GlendaloughDist 53.085011 -6.1074016 Mark Anthony Brands International
Great Northern Distillery Louth Carrickmacross Road, Dundalk, Co. Louth, Ireland, A91 P8W9 A91 P8W9 No https://gndireland.com/  @GNDistillery 54.001574 -6.40964 Teeling Family, formally of Cooley Distillery
Hinch Distillery Down 19 Carryduff Road, Boardmills, Ballynahinch, Down, United Kingdom BT27 6TZ No https://hinchdistillery.com/  @hinchdistillery 54.461021 -5.903713
Kilbeggan Distillery Westmeath Lower Main St, Aghamore, Kilbeggan, Co. Westmeath, Ireland N91 W67N Yes https://www.kilbegganwhiskey.com  @Kilbeggan 53.369369 -7.502809 Beam Suntory
Kinahan’s Distillery Dublin 44 Fitzwilliam Place, Dublin D02 P027 No https://kinahanswhiskey.com @KinahansLL Sources Whiskey from around ireland
Lough Gill Sligo Hazelwood Avenue, Cams, Co. Sligo F91 Y820 F91 Y820 Yes https://www.athru.com/  @athruwhiskey 54.255318 -8.433156
Lough Mask Mayo Drioglann Loch Measc Teo, Killateeaun, Tourmakeady, Co. Mayo F12 PK75 Yes https://www.loughmaskdistillery.com/  @lough_mask 53.611819 -9.444077 David Raethorne
Lough Ree Longford Main Street, Lanesborough, Co. Longford N39 P229 No https://www.lrd.ie  @LoughReeDistill 53.673328 -7.99043
Matt D’Arcy Down 27 St Marys St, Newry BT34 2AA, United Kingdom BT34 2AA No http://www.mattdarcys.com  @mattdarcys 54.172817 -6.339367
Midleton Distillery Cork Old Midleton Distillery, Distillery Walk, Midleton, Co. Cork.  P25 Y394 P25 Y394 Yes https://www.jamesonwhiskey.com/en-IE/visit-us/jameson-distillery-midleton  @jamesonireland 51.916344 -8.165174 Pernod Ricard
Nephin Mayo Nephin Whiskey Company, Nephin Square, Lahardane, Co. Mayo F26 W2H9 No http://nephinwhiskey.com/  @NephinWhiskey 54.029011 -9.32211
Pearse Lyons Distillery Dublin 121-122 James’s Street Dublin 8, D08 ET27 D08 ET27 Yes https://www.pearselyonsdistillery.com  @PLDistillery 53.343708 -6.289351
Powerscourt Wicklow Powerscourt Estate, Enniskerry, Co. Wicklow, A98 A9T7 A98 A9T7 Yes https://powerscourtdistillery.com/  @PowerscourtDist 53.184167 -6.190794
Rademon Estate Distillery Down Rademon Estate Distillery, Downpatrick, County Down, United Kingdom BT30 9HR Yes https://rademonestatedistillery.com  @RademonEstate 54.396039 -5.790968
Roe & Co Dublin 92 James’s Street, Dublin 8 D08 YYW9 Yes https://www.roeandcowhiskey.com 53.343731 -6.285673
Royal Oak Distillery Carlow Clorusk Lower, Royaloak, Co. Carlow R21 KR23 Yes https://royaloakdistillery.com/  @royaloakwhiskey 52.703341 -6.978711 Saronno
Scotts Irish Distillery Fermanagh Main Street, Garrison, Co Fermanagh, BT93 4ER, United Kingdom BT93 4ER No http://scottsirish.com 54.417726 -8.083534
Skellig Six 18 Distillery Kerry Valentia Rd, Garranearagh, Cahersiveen, Co. Kerry, V23 YD89 V23 YD89 Yes https://skelligsix18distillery.ie  @SkelligSix18 51.935701 -10.239549
Slane Castle Distillery Meath Slane Castle, Slane, Co. Meath C15 F224 Yes https://www.slaneirishwhiskey.com/  @slanewhiskey 53.711065 -6.562735 Brown-Forman & Conyngham Family
Sliabh Liag Donegal Line Road, Carrick, Co Donegal, F94 X9DX F94 X9DX Yes https://www.sliabhliagdistillers.com/  @sliabhliagdistl 54.6545 -8.633847
Teeling Whiskey Distillery Dublin 13-17 Newmarket, The Liberties, Dublin 8, D08 KD91 D08 KD91 Yes https://teelingwhiskey.com/  @TeelingWhiskey 53.337862 -6.277123 Teeling Family
The Quiet Man Derry 10 Rossdowney Rd, Londonderry BT47 6NS, United Kingdom BT47 6NS No http://www.thequietmanirishwhiskey.com/  @quietmanwhiskey 54.995344 -7.301312 Niche Drinks
The Shed Distillery Leitrim Carrick on shannon Road, Drumshanbo, Co. Leitrim N41 R6D7 No http://thesheddistillery.com/  @SHEDDISTILLERY 54.047145 -8.04358
Thomond Gate Distillery Limerick No https://thomondgatewhiskey.com/ @ThomondW Nicholas Ryan
Tipperary Tipperary Newtownadam, Cahir, Co. Tipperary No http://tipperarydistillery.ie/  @TippDistillery 52.358622 -7.881875
Tullamore Distillery Offaly Bury Quay, Tullamore, Co. Offaly R35 XW13 Yes https://www.tullamoredew.com  @TullamoreDEW 53.377774 -7.492944
Walsh Whiskey Distillery Carlow Equity House, Deerpark Business Park, Dublin Rd, Carlow R93 K7W4 No http://walshwhiskey.com/  @walshwhiskey 52.853417 -6.883916 Walsh Family
Waterford Distillery Waterford 9 Mary Street, Grattan Quay, Waterford City, Co. Waterford X91 KF51 No https://waterfordwhisky.com/  @waterforddram 52.264308 -7.120997 Renegade Spirits Ireland Ltd
Wayward Irish Distillery Kerry Lakeview House & Estate, Fossa Road, Maulagh, Killarney, Co. Kerry, V93 F7Y5 V93 F7Y5 No https://www.waywardirish.com  @wayward_irish 52.071045 -9.590709 O’Connell Fomily
West Cork Distillers Cork Marsh Rd, Marsh, Skibbereen, Co. Cork P81 YY31 No http://www.westcorkdistillers.com/  @WestCorkDistill 51.557804 -9.268941

Download Data Set

Irish_Whiskey_Distilleries – Excel Spreadsheet

Irish_Whiskey_Distilleries.csv – Zipped CSV file

I’ll be adding some additional formats soon.

Map of Distilleries

Here is a map with the Distilleries plotted using Google Maps.

Screenshot 2020-02-13 15.22.40

Twitter Lists & Twitter Hash Tags

I’ve created a Twitter list containing the twitter accounts for all of these distilleries. You can subscribe to the list to get all the latest posts from these distilleries

Irish Whishkey Distillery Twitter List

Have a look out for these twitter hash tags on a Friday, Saturday or Sunday night, as people from around the world share what whiskeys they are tasting that evening. Irish and Scotish Whiskies are the most common.

#FridayNightDram
#FridayNightSip
#SaturdayNightSip
#SaturdayNightDram
#SundayNightSip
#SundayNightDram

How to send me updates, corrections and details of Distilleries I’ve missed

Let me know, via the my Contact page,  if you see any errors in the data set, especially if I’m missing any distilleries.

Storing and processing Unicode characters in Oracle

Posted on Updated on

Unicode is a computing industry standard for the consistent encoding, representation, and handling of text expressed in most of the world’s writing systems (Wikipedia). The standard is maintained by the Unicode Consortium, and contains over 137,994 characters (137,766 graphic characters, 163 format characters and 65 control characters).

The NVARCHAR2 is Unicode data type that can store Unicode characters in an Oracle Database. The character set of the NVARCHAR2 is national character set specified at the database creation time. Use the following to determine the national character set for your database.

SELECT *
FROM nls_database_parameters
WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

For my database I’m using an Oracle Autonomous Database. This query returns the character set AL16UTF16. This character set encodes Unicode data in the UTF-16 encoding and uses 2 bytes to store a character.

When creating an attribute with this data type, the size value (max 4000) determines the number of characters allowed. The actual size of the attribute will be double.

Let’s setup some data to test this data type.

CREATE TABLE demo_nvarchar2 (
   attribute_name NVARCHAR2(100));

INSERT INTO demo_nvarchar2 
VALUES ('This is a test for nvarchar2');

The string is 28 characters long. We can use the DUMP function to see the details of what is actually stored.

SELECT attribute_name, DUMP(attribute_name,1016)
FROM demo_nvarchar2;

The DUMP function returns a VARCHAR2 value that contains the datatype code, the length in bytes, and the internal representation of a value.

 

You can see the difference in the storage size of the NVARCHAR2 and the VARCHAR2 attributes.

Valid values for the return_format are 8, 10, 16, 17, 1008, 1010, 1016 and 1017. These values are assigned the following meanings:


8 – octal notation
10 – decimal notation
16 – hexadecimal notation
17 – single characters
1008 – octal notation with the character set name
1010 – decimal notation with the character set name
1016 – hexadecimal notation with the character set name
1017 – single characters with the character set name

The returned value from the DUMP function gives the internal data type representation. The following table lists the various codes and their description.

Code Data Type
1 VARCHAR2(size [BYTE | CHAR])
1 NVARCHAR2(size)
2 NUMBER[(precision [, scale]])
8 LONG
12 DATE
21 BINARY_FLOAT
22 BINARY_DOUBLE
23 RAW(size)
24 LONG RAW
69 ROWID
96 CHAR [(size [BYTE | CHAR])]
96 NCHAR[(size)]
112 CLOB
112 NCLOB
113 BLOB
114 BFILE
180 TIMESTAMP [(fractional_seconds)]
181 TIMESTAMP [(fractional_seconds)] WITH TIME ZONE
182 INTERVAL YEAR [(year_precision)] TO MONTH
183 INTERVAL DAY [(day_precision)] TO SECOND[(fractional_seconds)]
208 UROWID [(size)]
231 TIMESTAMP [(fractional_seconds)] WITH LOCAL TIMEZONE

 

OCI Data Science – Create a Project & Notebook, and Explore the Interface

Posted on Updated on

In my previous blog post I went through the steps of setting up OCI to allow you to access OCI Data Science. Those steps showed the setup and configuration for your Data Science Team.

Screenshot 2020-02-11 20.46.42

In this post I will walk through the steps necessary to create an OCI Data Science Project and Notebook, and will then Explore the basic Notebook environment.

1 – Create a Project

From the main menu on the Oracle Cloud home page select Data Science -> Projects from the menu.

Screenshot 2020-02-12 12.07.19

Select the appropriate Compartment in the drop-down list on the left hand side of the screen. In my previous blog post I created a separate Compartment for my Data Science work and team. Then click on the Create Projects button.

Screenshot 2020-02-12 12.09.11Enter a name for your project. I called this project, ‘DS-Demo-Project’. Click Create button.

Screenshot 2020-02-12 12.13.44

Screenshot 2020-02-12 12.14.44

That’s the Project created.

2 – Create a Notebook

After creating a project (see above) you can not create one or many Notebook Sessions.

To create a Notebook Session click on the Create Notebook Session button (see the above image).  This will create a VM to contain your notebook and associated work. Just like all VM in Oracle Cloud, they come in various different shapes. These can be adjusted at a later time to scale up and then back down based on the work you will be performing.

The following example creates a Notebook Session using the basic VM shape. I call the Notebook ‘DS-Demo-Notebook’. I also set the Block Storage size to 50G, which is the minimum value. The VNC details have been defaulted to those assigned to the Compartment. Click Create button at the bottom of the page.

Screenshot 2020-02-12 12.22.24

The Notebook Session VM will be created. This might take a few minutes. When created you will see a screen like the following.

Screenshot 2020-02-12 12.31.21

3 – Open the Notebook

After completing the above steps you can now open the Notebook Session in your browser.  Either click on the Open button (see above image), or copy the link and share with your data science team.

Important: There are a few important considerations when using the Notebooks. While the session is running you will be paying for it, even if the session got terminated at the browser or you lost connect. To manage costs, you may need to stop the Notebook session. More details on this in a later post.

After clicking on the Open button, a new browser tab will open and will ask you to log-in.

Screenshot 2020-02-12 12.35.26

After logging in you will see your Notebook.

Screenshot 2020-02-12 12.37.42

4 – Explore the Notebook Environment

The Notebook comes pre-loaded with lots of goodies.

The menu on the left-hand side provides a directory with lots of sample Notebooks, access to the block storage and a sample getting started Notebook.

Screenshot 2020-02-12 12.41.09

When you are ready to create your own Notebook you can click on the icon for that.

Screenshot 2020-02-12 12.42.50

Or if you already have a Notebook, created elsewhere, you can load that into your OCI Data Science environment.

Screenshot 2020-02-12 12.44.50

The uploaded Notebook will appear in the list on the left-hand side of the screen.