Hello OmniSci

This topic demonstrates how you can:
​Clone the mapd-connector repository.
​Connect to a OmniSci server instance.
​Retrieve the results of a SQL query.
​Display the results on an HTML page.
Clone
Much of the underlying infrastructure you need to interact with a OmniSci server is provided by the mapd-connector repository. You can clone the repository locally to take advantage of its utilities.
To clone the mapd-connector repository:
1.
Go to https://github.com/omnisci/mapd-connector.
2.
Click Clone or Download.
3.
Choose whether to clone the repository using HTTPS or SSH, or optionally download a Zip archive to expand on your local drive.
For more information on cloning GitHub repositories, see https://help.github.com/articles/cloning-a-repository/​
Open mapd-connector/examples/browser.html in a web browser to connect to a sample OmniSci server instance and display information from the sample database. You can open mapd-connector/examples/browser.js in a text editor and follow along as the following sections describe how the script connects, retrieves, and displays data from the OmniSci server.
Connect
The lynchpin of the application is establishing a connection to OmniSci. Fortunately, the MapdCon object does the heavy lifting for you. You provide the location and port of the OmniSci host and your user credentials to create a connection.
1
const connector = new window.MapdCon()
2
​
3
connector
4
  .protocol("https")
5
  .host("metis.mapd.com")
6
  .port("443")
7
  .dbName("omnisci")
8
  .user("admin")
9
  .password("HyperInteractive")
10
  .connectAsync()
11
​
Copied!
Retrieve
Connecting to the OmniSci server is not the final goal, of course. Once you have a connection, you can send SQL queries to retrieve data.
First, create constant variables for your SQL queries.
1
// The total number of tweets from Columbia
2
const query = "SELECT count(*) AS n FROM tweets_nov_feb WHERE country='CO'"
3
// try changing airtime to arrdelay in the query
4
const query2 =
5
  "SELECT carrier_name as key0, AVG(airtime) AS val FROM flights_donotmodify WHERE airtime IS NOT NULL GROUP BY key0 ORDER BY val DESC LIMIT 100"
Copied!
Create an empty array for your query options.
1
const defaultQueryOptions = {}
Copied!
Connect as shown above.
1
const connector = new window.MapdCon()
2
​
3
connector
4
  .protocol("https")
5
  .host("metis.mapd.com")
6
  .port("443")
7
  .dbName("omnisci")
8
  .user("admin")
9
  .password("HyperInteractive")
10
  .connectAsync()
Copied!
Use a promise to send a series of queries, including the ones defined as constants.
1
  .then(session =>
2
    // now that we have a session open we can make some db calls:
3
    Promise.all([
4
      session.getTablesAsync(),
5
      session.getFieldsAsync("flights_donotmodify"),
6
      session.queryAsync(query, defaultQueryOptions),
7
      session.queryAsync(query2, defaultQueryOptions)
8
    ])
9
  )
Copied!
Display
Promise returns a values object with an array of query results. The first item in the values array is a list of tables. Write the results to the browser console.
1
  .then(values => {
2
    console.log(
3
      "All tables available at metis.mapd.com:",
4
      values[0].map(x => x.name)
5
    )
Copied!
The second item in the array contains a list of fields in the table flights_donotmodify.
1
    // handle result of getFieldsAsync
2
    console.log(
3
      "All fields for 'flights_donotmodify':",
4
      values[1].reduce((o, x) => Object.assign(o, { [x.name]: x }), {})
5
    )
Copied!
The third query contains a count of Twitter™ tweets from Columbia. Display that in the HTML page using the DOM.
1
    // handle result of first query
2
    document.getElementById("result-async").innerHTML =
3
      "There are " + values[2][0].n + " tweets from Columbia."
4
    console.log("Query 1 results:", Number(values[2][0].n))
Copied!
The final query returns the average air time for flights listed in the flights_donotmodify table, with a limit of 100 records. Display the results as a chart using the createRowChart method.
1
    // handle result of 2nd query
2
    createRowChart(values[3])
3
    console.log(
4
      "Query 2 results:",
5
      values[3].reduce((o, x) => Object.assign(o, { [x.key0]: x.val }), {})
6
    )
7
  })
8
  .catch(error => {
9
    console.error("Something bad happened: ", error)
10
  })
Copied!
Use the array to create a chart using the D3 API. This example is based on an example from the free ebook D3 Tips and Tricks​
1
function createRowChart(data) {
2
  var margin = { top: 20, right: 20, bottom: 150, left: 40 },
3
    width = 600
4
  height = 300
5
​
6
  var x = d3.scale.ordinal().rangeRoundBands([0, width], 0.05)
7
​
8
  var y = d3.scale.linear().range([height, 0])
9
​
10
  var xAxis = d3.svg
11
    .axis()
12
    .scale(x)
13
    .orient("bottom")
14
    .tickFormat(function(d, i) {
15
      return d
16
    })
17
​
18
  var yAxis = d3.svg
19
    .axis()
20
    .scale(y)
21
    .orient("left")
22
    .ticks(10)
23
​
24
  var svg = d3
25
    .select("#chart")
26
    .append("svg")
27
    .attr("width", width + margin.left + margin.right)
28
    .attr("height", height + margin.top + margin.bottom)
29
    .append("g")
30
    .attr("transform", "translate(" + margin.left + "," + margin.top + ")")
31
​
32
  x.domain(
33
    data.map(function(d) {
34
      return d.key0
35
    })
36
  )
37
  y.domain([
38
    0,
39
    d3.max(data, function(d) {
40
      return d.val
41
    })
42
  ])
43
​
44
  svg
45
    .append("g")
46
    .attr("class", "x axis")
47
    .attr("transform", "translate(0," + height + ")")
48
    .call(xAxis)
49
    .selectAll("text")
50
    .style("text-anchor", "end")
51
    .attr("dx", "-.8em")
52
    .attr("dy", "-.55em")
53
    .attr("transform", "rotate(-90)")
54
​
55
  svg
56
    .append("g")
57
    .attr("class", "y axis")
58
    .call(yAxis)
59
    .append("text")
60
    .attr("transform", "rotate(-90)")
61
    .attr("y", 6)
62
    .attr("dy", ".71em")
63
    .style("text-anchor", "end")
64
​
65
  svg
66
    .selectAll("bar")
67
    .data(data)
68
    .enter()
69
    .append("rect")
70
    .style("fill", "steelblue")
71
    .attr("x", function(d) {
72
      return x(d.key0)
73
    })
74
    .attr("width", x.rangeBand())
75
    .attr("y", function(d) {
76
      return y(d.val)
77
    })
78
    .attr("height", function(d) {
79
      return height - y(d.val)
80
    })
81
}
Copied!

Tutorials - Previous

Using OmniSci Immerse

Next - Tutorials

Creating a Kafka Streaming Application

Last modified 1yr ago

Export as PDF

Copy link

Contents