CORP707 – 1 - IoT Database Fundamental.pdf

CORP707 – IoT Database Fundamental What is IoT IoT is a tool or a method. The essential quality of an IoT system lies in how much you can innovate by effectively using this tool. IoT means adding value to the way we live and do business by connecting not just computers and smartpho...

CORP707 – IoT Database Fundamental What is IoT IoT is a tool or a method. The essential quality of an IoT system lies in how much you can innovate by effectively using this tool. IoT means adding value to the way we live and do business by connecting not just computers and smartphones, but various devices to the internet.  Payments Remote monitoring Suitable Security Applications of Sensing IoT Smart meters Telematics Tracing Building an IoT System File Formats Proprietary Open File Formats for IoT Devices CSV (Comma Separated Values) XML (eXtensible Markup Language) JSON (JavaScript Object Notation) Binary Formats Comma Separated Values The textual content within this type of file uses commas to separate one item of data from another (sometimes semicolons, TAB characters or even spaces) For example: Last,First,Age,Weight Smith,Bill,27,155 Jones,Sue,31,125 Boogie,Bobby,35,140 This type of data can be created programmatically, or using tools like Excel Comma Separated Values Problem: What if the data you’re trying to separate contains a comma? Solution: put data inside double quotes For example: AuthorLast,AuthorFirst,Title Boogie,Bobby,"Money, and how to Spend It!" Jones,Sue,"My Life, In Pictures" But this creates further problems! Comma Separated Values Problem: What if the data you’re trying to separate contains a double quote? Solution: “escape” the double quote character with an extra double quote! For example: Year,Make,Model,Description 2001,Ford,F150,"base model" 2005,Chevy,Venture,"Extended Edition ""Must Sell""" Reading and processing this complexity can be a challenge! Comma Separated Values Some issues arise using this technique! What if the data you’re trying to separate contains a comma? Solution: put data inside double quotes For example: AuthorLast,AuthorFirst,Title Boogie,Bobby,”Money, and how to Spend It!” Jones,Sue,”My Life, In Pictures” But this creates further problems! Parsing CSV files in Javascript // This assumes no commas in the values names. function getCsvValuesFromLine(line) { var values = line.split(','); value = values.map(function(value){ return value.replace(/\"/g, ''); }); return values; } var headers = getCsvValuesFromLine(lines); XML A final file format often seen is XML, which stands for eXtensible Markup Language A Markup Language is a text encoding mechanism that specifies the structure and formatting of a document (or data) and in some caes, relationships between its parts HTML can be thought of as a subset of XML, but suffers from “sloppy” markup adherence XML can be used to support storage, transmission and reconstruction of arbitrary data – which can also be supported by JSON Where XML differs is it’s “tree structured” approach to defining data relationships. The tree has a “root”, with branches called “child nodes”, each holding “subchild nodes”, and each level of nesting allowing for “attributes” to be specified XML The general syntax for XML is:..... XML As an example, in the diagram to the right, a classic tree structuring of information exists, documenting a bookstore selling books The “bookstore” has a child node called “book” (with attributes such as the category of book, like “cookbook”) A book has a series of sibling child nodes representing details of the book: “title”, “author”, “year”, “price” XML The resulting XML could be: Fantastic French Foods John Smith 2015 29.99... Parsing XML files in Javascript JSON More recently, to support arbitrary data exchange in web based environments, a type of text file mechanism called JSON (JaveScript Object Notation) has evolved It’s designed for small file size and fast data transfer, with minimal overhead on processing compared to other file formats discussed Example: { "name":"John", "age":30, "car":null } This example shows three keys or “properties”: name, age and car; with associated data (or “values”): John, 30 and “nothing” Note the bounding curly braces { and } that surround the data definition JavaScript and TypeScript both have native methods to work with JSON data JSON More complex objects can be described using JSON: { "employee": { "name": "sonoo", "salary": 56000, "married": true } } JSON data is bounded by curly braces { and }, string data in double quotes (such as the properties in the object), numerical values do not need quoting. Booleans are supported too. JSON JSON syntax is derived from JavaScript object notation syntax: Data is in property/value (key/data) pairs, e.g. "name":"John" Data is separated by commas, e.g. "name":"John", "age":30 Curly braces hold objects, e.g. { "name":"John", "age":30 } Square brackets hold arrays, e.g. { "name":"John", "age":30, "cars":["Ford", "BMW", "Fiat"] } Parsing JSON files in Javascript Binary Format Image Audio Video Blob  Data includes Format Pros Cons definition? Comparison between JSON Easy to read and write, widely supported, language-independent Less efficient for binary data, can be verbose Yes File Format XML Human-readable, self- descriptive, widely used in enterprise Verbose, larger file sizes compared to JSON Yes Not suitable for complex Simple, ideal for tabular CSV or hierarchical data Yes data, widely supported structures Binary format, less Efficient and compact, human-readable, requires Binary No faster than JSON separate schema definition  Parameter Type Description The status of the device. Valid values: Simple Transmission status String offline iotId String The unique identifier of the device in IoT Platform. offlineReasonCode Integer The error code that is returned when the device goes offline Data The unique identifier of the product to which the device productKey String belongs. deviceName String The DeviceName of the device. lastTime String These parameters are no longer valid. utcLastTime String The time when the device was online or offline. The returned messages are not sorted by time. You need to manually sort the messages. time String For example, you received the following messages in sequence: Online time: 2018-08-31 10:02:28.195 The preceding messages indicate that the device was disconnected, reconnected, and then disconnected again. The time when the device went online or offline. The time is in utcTime String the UTC format. clientIp String The public IP address of the device. GraphQL “GraphQL is a query language for APIs and a runtime for fulfilling those queries with your existing data” GraphQL “GraphQL is a query language for APIs and a runtime for fulfilling those queries with your existing data” GraphQL Type System Object Types Scalars Interfaces String Float Unions Int Enumerations Boolean Fields ID Custom Scalar e.g. Date Lists Runnable GraphQL server code const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); const query = `query { serverTime }`; query = 'query { serverTime }' graphql(schema, query).then(result =>{ result = schema.execute(query) console.log(result.data); print(result.data) }); #: Prints: OrderedDict([('serverTime', '2017-07-24 // Prints: 19:10:38.127089')]) // {serverTime: "9/5/2016, 6:28:46 PM"} Runnable GraphQL server code / Defining API shape (GraphQL schema) const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); const query = `query { serverTime }`; query = 'query { serverTime }' graphql(schema, query).then(result =>{ result = schema.execute(query) console.log(result.data); print(result.data) }); #: Prints: OrderedDict([('serverTime', '2017-07-24 // Prints: 19:10:38.127089')]) // {serverTime: "9/5/2016, 6:28:46 PM"} Runnable GraphQL server code / Query in GraphQL const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); const query = `query { serverTime }`; query = 'query { serverTime }' graphql(schema, query).then(result =>{ result = schema.execute(query) console.log(result.data); print(result.data) }); #: Prints: OrderedDict([('serverTime', '2017-07-24 // Prints: 19:10:38.127089')]) // {serverTime: "9/5/2016, 6:28:46 PM"} Runnable GraphQL server code / Query Execution (query + schema → result) const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); const query = `query { serverTime }`; query = 'query { serverTime }' graphql(schema, query).then(result =>{ result = schema.execute(query) console.log(result.data); print(result.data) }); #: Prints: OrderedDict([('serverTime', '2017-07-24 // Prints: 19:10:38.127089')]) // {serverTime: "9/5/2016, 6:28:46 PM"} Runnable GraphQL server code / Result const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); const query = `query { serverTime }`; query = 'query { serverTime }' graphql(schema, query).then(result =>{ result = schema.execute(query) console.log(result.data); print(result.data) }); #: Prints: OrderedDict([('serverTime', '2017-07-24 // Prints: 19:10:38.127089')]) // {serverTime: "9/5/2016, 6:28:46 PM"}  Parts in GraphQL server GraphQL Server Query Entry point Result query { serverTime } JS: graphql(schema, query) { data: { python: schema.execute(query) serverTime: "..." } } Schema GraphQL server over HTTP const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); const query = `query { serverTime }`; query = 'query { serverTime }' graphql(schema, query).then(result =>{ result = schema.execute(query) console.log(result.data); print(result.data) }); GraphQL server over HTTP const { import graphene graphql, GraphQLSchema, GraphQLObjectType, from datetime import datetime GraphQLString, } = require('graphql'); const schema = new GraphQLSchema({ class Query(graphene.ObjectType): query: new GraphQLObjectType({ server_time = graphene.Field(graphene.String) name: 'Query', fields: { def resolve_server_time(obj, args, context, info): serverTime: { return str(datetime.now()) type: GraphQLString, resolve() { schema = graphene.Schema(query=Query) return (new Date).toLocaleString(); }, }, }, }) }); app.post('/graphql', (req, res) => { @app.route('/graphql') graphql(schema, req.body).then(res.json); def graphql(): }); result = schema.execute(request.body) return json.dumps({ data: result.data, errors: result.errors }) Ready-made GraphQL Server library NodeJS Python express-graphql Flask-GraphQL Apollo Server (real-world example) Running Example Clone https://github.com/appier/graphql-cookbook and follow the README GraphiQL GraphQL Results & error handling Questions