Ignite SQL Getting Started

资料

Getting Started

Ignite SQL Getting Started

SUGGEST EDITS

Overview

Today, SQL is still a very popular language for data definition, data manipulation and querying in database management systems. Although often associated with Relational database systems, it is now used far more widely with many non-Relational database systems also supporting SQL to varying degrees. Furthermore, there is a huge market for a wide-range of SQL-based tools that can provide visualization, reports and business intelligence. These use standards such as ODBC and JDBC to connect to data sources.

Apache Ignite supports Data Definition Language (DDL) statements for creating and removing SQL tables and indexes at runtime and Data Manipulation Language (DML) for performing queries. Both native Apache Ignite SQL APIs as well as JDBC and ODBC drivers can be used.

The following examples will use a schema consisting of two tables. These tables are used to hold information about a city and the people that live there. The assumption is that a city may have many people and people will live in only one city. This is a one-to-many (1:m) relationship.

Connectivity

The getting started is designed to be used with a SQL tool of your choice or from source code. For instance, if you prefer the tools way then refer to SQL Tooling page to see a quick example on how to set up the SQL tool.

If you wish to work from the source code, the following code examples show how to use the JDBC and ODBC drivers and open a connection:

// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

The JDBC connection uses the thin driver and connects to localhost (127.0.0.1). Ensure that the file ignite-core.jar is in the classpath of an application or tool. See the JDBC Driver documentation for more details.

The ODBC connection is to localhost, port 10800. See the ODBC Driver documentation for more details.

No matter of the approach you choose, open a preferred command line tool, go to {apache-ignite-version}/bin, and execute the ignite.sh or ignite.bat script in order to start one or more cluster nodes:

./ignite.sh

Create Tables

Presently, every table created ends up in PUBLIC schema. Read more in Schema and Indexes section.

Let's create a database schema for our cities and people objects. Here are examples of how the two tables can be created:​

CREATE TABLE City (
  id LONG PRIMARY KEY, name VARCHAR)
  WITH "template=replicated"

CREATE TABLE Person (
  id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id))
  WITH "backups=1, affinityKey=city_id"

Once the CREATE TABLE command is executed, the following happens:

  • A new distributed cache is created automatically using the name of the table as its name. The caches store objects of type city and person that can correspond to specific Java, .NET, C++ class or Binary Object objects.
  • An SQL table with all the parameters set will be defined.
  • The data will be stored in key-value records. The primary key column will be used as an object's key and the remaining fields will belong to the value. This means that you can also work with the data using the key-value APIs.

Distributed cache related parameters are passed in the WITH clause of the statement. If the WITH clause is omitted, then the cache will be created with default parameters set in CacheConfiguration object.

In the above example, for the Person table, Ignite creates a distributed cache with 1 backup of data and city_id as the affinity key. These extended parameters are Ignite specific that can be passed using the WITH clause. To set other cache configurations for the table, you should use the template parameter and provide the name of the cache configuration previously registered(via XML or code). See extended parameterssection for more details.

In many cases it is beneficial to collocate different cache keys together if they will be accessed together. Quite often, business logic will require access to more than one cache key. By collocating them together we can ensure that all keys with the same affinityKey will be cached on the same processing node, hence avoiding costly network trips to fetch data from remote nodes.

In the example schema, we have City and Person objects and we want to collocate Person objects with City objects for where a person lives. To achieve this, we use the WITH clause and specify affinityKey=city_id as shown above.

Create Indexes

Now, let's define several indexes in order to accelerate query lookup. Here are examples of how the indexes can be created:​

CREATE INDEX idx_city_name ON City (name)

CREATE INDEX idx_person_name ON Person (name)

Inserting Data

Next, in order to query data, we need to load some values into the two tables. Here are several examples of how data can be inserted into the tables:

INSERT INTO City (id, name) VALUES (1, 'Forest Hill');
INSERT INTO City (id, name) VALUES (2, 'Denver');
INSERT INTO City (id, name) VALUES (3, 'St. Petersburg');

INSERT INTO Person (id, name, city_id) VALUES (1, 'John Doe', 3);
INSERT INTO Person (id, name, city_id) VALUES (2, 'Jane Roe', 2);
INSERT INTO Person (id, name, city_id) VALUES (3, 'Mary Major', 1);
INSERT INTO Person (id, name, city_id) VALUES (4, 'Richard Miles', 2);

Querying Data

Once we have loaded some data, we can perform queries so that we can answer some questions. Here are examples of how data can be selected, including joining across two tables:

SELECT *
FROM City

SELECT name
FROM City
WHERE id = 1

SELECT p.name, c.name
FROM Person p, City c
WHERE p.city_id = c.id

Modifying Data

Sometimes data may change and we can perform an update operation to modify existing values. Here are examples of how data can be updated:

UPDATE City
SET name = 'Foster City'
WHERE id = 2

Removing Data

Finally, we may need to remove objects from our database. Here are examples of how data can be deleted:

DELETE FROM Person
WHERE name = 'John Doe'

Examples

Some complete SQL code examples used in this getting started guide are available on GitHub.

 

来源：https://apacheignite.readme.io/docs/getting-started-sql

 

https://github.com/VeryFatBoy/Ignite-SQL-Getting-Started