[Feedback] Chapter 3 - CRUD Operations

[AnggaSuherman]

Overview of chapter 3; This chapter should explain about the basic CRUD operations.

• First is to explain the data model of GridDB, then import existing data (use IoT sample data, same structure as explained in data model, with the import tool). Then a simple query on the existing data. Do we have an explanation of the batch ingestion using mput somewhere?
• In addition to the ingestion of existing data, user will do the simple CRUD, so explain using gs_sh how to create container, put, update, delete, and read. Explain CRUD via NoSQL interface (faster way of doing things), and also the SQL interface (more convenient) in gs_sh.
• Then explain the equivalent CRUD operations via the supported programming languages: Java, C, JDBC, Python, etc. and WebAPI.

---

• Data model
• (Comment 8/30) Import, Export and Query data: This should be inserted here.

  • I think this data set is good. It has 3 devices, each storing a few sensors. Please consult with Owen about this, but I think the data model would be 1 container collection storing the information for the devices (name, attributes, location, etc. not included but you can make it up), and 3 time series container for each device.

  • I think you need to change the format prior to importing.

  • Do it all in CLI.

  • Perform some simple queries, e.g. max/min of the Carbon Dioxide, average temperature in the morning, afternoon, etc.)

  • (Added 9/14) - I think you mentioned that you were going to ingest the data using a script? . This section is not about import/export tool, but how to import, export, query existing data to GridDB. So prior to the import/export installation, let's explain that in this section we will be importing existing data that we get from Kaggle (show the actual steps from the beginning, e.g. going to kaggle, downloading the file, etc.)

    • You need to do data cleansing here, as I mentioned the file contains sensors from 3 devices. In GridDB this will be stored to 3 separate time series container + 1 container for the actual device information.
    • First step: go to kaggle, download the csv, explain a little bit about the data (7 sensors in 1 device, and we have 3 of those).
    • As you just explained about data model in the previous chapter, current section is about that implementation. So in this case, as per GridDB recommendation (that each device to be stored as 1 time series container) you need to separate the 1 csv into 3 csvs, per device (00:0f:00:70:91:0a, 1c:bf:ce:15:ec:4d, b8:27:eb:bf:9d:51) -> please confirm with Owen but I think this should be the name of the time series containers.
    • In addition, I think you need to change the time format to GridDB format (please ask owen if there is an easy way to do this)
    • Then, prepare the 3 import files , e.g. public.00:0f:00:70:91:0a.csv -> for this it is ok to pretend that you did the hard work but you actually use your script and generate these files using export instead (hide this though). Please explain how to make the files though (e.g. the current data is already in "...","..." format so you just need to add the GridDB headers to the public.00:0f:00:70:91:0a.csv , plus explain about the properties.json)
    • Once the data preparation is done, then start the installation of imp/exp tool and actually import those 3 devices.
    • Show that they have been imported successfully, and perform simple select query.
    • Then add a collection container containing the metadata of these devices (via gs_sh is ok).
    • Explain that if you want to upgrade from say GridDB V4.6 to V5.0, then you can use the export tool then import to do that.

    -It is ok to do it all at once and separate the video to 3, but please make sure that you put a break /pause in between so that the cut is smooth:

    • Data preparation
    • Import
    • Export
• (Comment 9/2) Add CRUD explanation via gs_sh
  • Explain that you can either use the web shell on docs.griddb.net or follow the GridDB server installation video on chapter 2
  • Content:
    • Explain about the NoSQL Interface such as put, get, TQL query. NoSQL interface is for simple/basic data access, very fast data ingestion and search, but a GridDB specific API. Perform collection and time series container creation, put to these containers, do a get, update a row, delete a row and simple query using TQL.
    • Explain about the SQL interface. GridDB SQL is SQL-92 compliant, standard API, equipped with parallel distributed processing. Perform create table and timeseries table, insert, delete, update, select.
    • I think it is better to use similar data model as the one you ingested (the environmental sensor data), and continue adding new data. e.g. Creating a time series container for the 4th device. Deleting the container collection for device and replace with a new one. Put new rows to existing device and new one. Update past data, e.g. erroneous data.
• JDBC/SQL
  • Comment (9/2): Video
    • Amplify your voice.
    • The content is OK. Although I think JDBC is basically mainly used when you want to connect to another application via standard interface like Tableau, PowerBI and the SQL Workbench, so I think it is better to move this to Chapter 5 as a supplementary.
• API
  • Comment 9/2 - Python API
    • Amplify your voice
    • Bigger text
    • Should be fixed list when you start via systemctl, you are still using the multicast.
    • Use the same data model as the environmental sensor data. e.g. create 5th new device.
    • There should be a separate course for GridDB python alone. But for now, only the simple CRUD and connection are sufficient. No need to use these samples as is but based on it create new ones with the new environmental sensor data model. Also explain how to install using pip.
      • Use fresh installation so you know what are missing/need to be installed.
      • Installation using PIP
      • Connection : explain the three methods. Also explain that the default GridDB systemctl installation is using fixed list.
      • How to create Collection Container, and Time Series Container
      • How to Put rows and Get Rows
      • How to Delete rows
      • How to Update rows using TQL
      • Query using TQL
  • Comment 9/2 - Java API
    • Amplify your voice
    • Bigger text
    • Should be fixed list when you start via systemctl, you are still using the multicast.
    • Use the same data model as the environmental sensor data. e.g. create 6th new device.
    • Same as Python: explain connectivity, create collections, CRUD, Query using TQL.
    • It is better to use the same data model throughout, to get the flow right. Currently the sample program does not really showcase the IoT, time series data. Having said that I think the current video is good and can be uploaded to youtube as is as a separate video.
• WebAPI
  • Comment 9/2 Video
    • Amplify your voice
    • Bigger text
    • Should be fixed list when you start via systemctl, you are still using the multicast and defaultCluster.
    • How about using postman to showcase the CRUD. Again we need to cover at least create container, put, get, delete, TQL and SQL. Please refer to this specification instead.
    • Again let's apply these CRUD operations to the existing environmental sensor data.

Import/Export:

• You went back and forth with the settings: please prepare the script before hand so you just need to copy and paste.
• I think you mentioned that you were going to ingest the data using a script? . This section is not about import/export tool, but how to import, export, query existing data to GridDB. So prior to the import/export installation, let's explain that in this section we will be importing existing data that we get from Kaggle (show the actual steps from the beginning, e.g. going to kaggle, downloading the file, etc.)
  • You need to do data cleansing here, as I mentioned the file contains sensors from 3 devices. In GridDB this will be stored to 3 separate time series container + 1 container for the actual device information.
  • First step: go to kaggle, download the csv, explain a little bit about the data (7 sensors in 1 device, and we have 3 of those).
  • As you just explained about data model in the previous chapter, current section is about that implementation. So in this case, as per GridDB recommendation (that each device to be stored as 1 time series container) you need to separate the 1 csv into 3 csvs, per device (00:0f:00:70:91:0a, 1c:bf:ce:15:ec:4d, b8:27:eb:bf:9d:51) -> please confirm with Owen but I think this should be the name of the time series containers.
  • In addition, I think you need to change the time format to GridDB format (please ask owen if there is an easy way to do this)
  • Then, prepare the 3 import files , e.g. public.00:0f:00:70:91:0a.csv -> for this it is ok to pretend that you did the hard work but you actually use your script and generate these files using export instead (hide this though). Please explain how to make the files though (e.g. the current data is already in "...","..." format so you just need to add the GridDB headers to the public.00:0f:00:70:91:0a.csv , plus explain about the properties.json)
  • Once the data preparation is done, then start the installation of imp/exp tool and actually import those 3 devices.
  • Show that they have been imported successfully, and perform simple select query.
  • Then add a collection container containing the metadata of these devices (via gs_sh is ok).
  • Explain that if you want to upgrade from say GridDB V4.6 to V5.0, then you can use the export tool then import to do that.

It is ok to do it all at once and separate the video to 3, but please make sure that you put a break /pause in between so that the cut is smooth:

• Data preparation
• Import
• Export

---

Final Video for Chapter 3
Comment 9/20

• Data model DONE
• Importing existing data via Import tool DONE
• Alternative way of importing existing data via Node.js client DONE
• Exporting data for migration DONE
• GridDB CRUD (explained via gs_sh) DONE
• GridDB API
  • Java DONE
  • JDBC DONE
  • Python DONE
  • Web API DONE

[AnggaSuherman]

Israel, the following also did not have sound:

• JDBC hands-on: https://www.youtube.com/watch?v=PRqdVctDJ9s&list=PLMg7lY2Lux08KRdbeLZj3tV5NfUv-CqvU&index=2&t=152s
• API Java and API Python
• Web API First and Second

[Imisrael]

Thank you, I have remade the videos using different editing software to remove the system sounds audio track

JDBC - https://youtu.be/mtRcmVDI2Hs
Python API - https://www.youtube.com/watch?v=iDfhfOpeVyQ
Java API - https://youtu.be/F4V-DaOPdAc
WebAPI - https://youtu.be/X-9HgJ7KwmY

[AnggaSuherman]

Israel, I am done with my comments for this chapter 3. Please see the comments at the top.
Sorry, there are many changes, but this should be the final iteration/edits.
This chapter is I think the most important one (very hands on and interesting), so please take your time.
Please be careful with the text size, sound, and unexpected errors to avoid rework.

[Imisrael]

Import/Export

I made this as one video initially but ended up splitting it up because it got too long. Please let me know if the split is Ok

And let me know if the import is OK too... i ran into some confusion (the tool isn't perfectly straightforward)

Export - https://youtu.be/o9qoPw1gqFs
Import - https://youtu.be/xOp7VGc7WIc

@AnggaSuherman

[AnggaSuherman]

Thank you Israel,

• The export video did not cut well.
• You went back and forth with the settings: please prepare the script before hand so you just need to copy and paste.
• I think you mentioned that you were going to ingest the data using a script? . This section is not about import/export tool, but how to import, export, query existing data to GridDB. So prior to the import/export installation, let's explain that in this section we will be importing existing data that we get from Kaggle (show the actual steps from the beginning, e.g. going to kaggle, downloading the file, etc.)
  • You need to do data cleansing here, as I mentioned the file contains sensors from 3 devices. In GridDB this will be stored to 3 separate time series container + 1 container for the actual device information.
  • First step: go to kaggle, download the csv, explain a little bit about the data (7 sensors in 1 device, and we have 3 of those).
  • As you just explained about data model in the previous chapter, current section is about that implementation. So in this case, as per GridDB recommendation (that each device to be stored as 1 time series container) you need to separate the 1 csv into 3 csvs, per device (00:0f:00:70:91:0a, 1c:bf:ce:15:ec:4d, b8:27:eb:bf:9d:51) -> please confirm with Owen but I think this should be the name of the time series containers.
  • In addition, I think you need to change the time format to GridDB format (please ask owen if there is an easy way to do this)
  • Then, prepare the 3 import files , e.g. public.00:0f:00:70:91:0a.csv -> for this it is ok to pretend that you did the hard work but you actually use your script and generate these files using export instead (hide this though). Please explain how to make the files though (e.g. the current data is already in "...","..." format so you just need to add the GridDB headers to the public.00:0f:00:70:91:0a.csv , plus explain about the properties.json)
  • Once the data preparation is done, then start the installation of imp/exp tool and actually import those 3 devices.
  • Show that they have been imported successfully, and perform simple select query.
  • Then add a collection container containing the metadata of these devices (via gs_sh is ok).
  • Explain that if you want to upgrade from say GridDB V4.6 to V5.0, then you can use the export tool then import to do that.

It is ok to do it all at once and separate the video to 3, but please make sure that you put a break /pause in between so that the cut is smooth:

• Data preparation
• Import
• Export

I hope my explanation is not confusing.

[AnggaSuherman]

Also we will be providing these files to the users (so they do not need to do the hard work) so please add all the files you have created, used to this repository

[AnggaSuherman]

@Imisrael , are you OK with my comments about the data set? I think this is the prerequisites to other video as you have to use these data throughout, so needs to be prioritised. Please let me know if you have any issue.

[Imisrael]

@AnggaSuherman yes no problem with dataset.

Also I think maybe for this issue it may be best if you could re-make it with all comments better laid out? As of right now it's kind of messy with lots of comments from both of us splitting up the feedbacks

[AnggaSuherman]

@Imisrael The main comments should be the one at the top of this page so please refer to that. The comments underneath are simply the details and out interactions.

[Imisrael]

@AnggaSuherman okay awesome thanks! but can you add the Import/Export part there please in case I lose track

edit: scratch that, I just saw I can edit the top comment as well so I can add whatever notes I need

[AnggaSuherman]

I added the details comment about import/export and added a timestamp when I updated the top comment

[Imisrael]

@AnggaSuherman Actually you were right... there is an issue with the dataset you chose. the timestamp column has messed up values. I tried messing with it trying to convert it with Java but i think it's easier to just find a dataset with a better value in the time column

java.lang.NumberFormatException: For input string: "1.5947050235678918E9"

The value doesn't display the full time stamp... it displays a shortened one that Java does not like

https://www.kaggle.com/datasets/garystafford/environmental-sensor-data-132k

If you look at the timestamp values, they use shortened E notation

[AnggaSuherman]

@Imisrael It is in unix epoch. I was able to convert it in excel actually. I am sure there is an equivalent converter for Java?

[Imisrael]

yeah it's in Unix epoch but it's shortened in the CSV file. it comes out with the exponent E. in excel it transforms into the full value but not in java. in java it's a string in quotes with an E.

i suppose I can try to look into a way to convert but it adds extra work load