文档章节

voltdb常用命令以及常用概念和常用方法

Oscarfff
 Oscarfff
发布于 2016/06/10 16:27
字数 2285
阅读 388
收藏 0

1、创建数据库

   voltdb create;##注意这个命令是初始化数据库的意思,所以,如果你之前初始化过,再用的话,会将之前文件删除的,回到初始状态。

2、关闭数据库

   voltadmin shutdown;

3、暂停数据库

   voltadmin pause ##数据库拒绝连接请求

4、恢复或者重启数据库

   voltdb recover

5、导入数据库配置

$ sqlcmd < myschema.sql;

或者
$ sqlcmd
1> file myschema.sql;

或者

$ sqlcmd
1>
2> CREATE TABLE Customer (
3> CustomerID INTEGER UNIQUE NOT NULL,
4> FirstName VARCHAR(15),
5> LastName VARCHAR (15),
6> PRIMARY KEY(CustomerID)
7> );

6、建表和主键

Example 4.1. DDL Example of a Reservation Schema


CREATE TABLE Flight (
FlightID INTEGER UNIQUE NOT NULL,
DepartTime TIMESTAMP NOT NULL,
Origin VARCHAR(3) NOT NULL,
Destination VARCHAR(3) NOT NULL,
NumberOfSeats INTEGER NOT NULL,
PRIMARY KEY(FlightID)
);


CREATE TABLE Reservation (
ReserveID INTEGER NOT NULL,
FlightID INTEGER NOT NULL,
CustomerID INTEGER NOT NULL,
Seat VARCHAR(5) DEFAULT NULL,
Confirmed TINYINT DEFAULT '0'
);


CREATE TABLE Customer (
CustomerID INTEGER UNIQUE NOT NULL,
FirstName VARCHAR(15),
LastName VARCHAR (15),
PRIMARY KEY(CustomerID)   ###建表的时候指定主键
);

$ sqlcmd                                  ##如果是多个字段联合主键就用下面的命令
1> CREATE TABLE Customer (
2> FirstName VARCHAR(15),
3> LastName VARCHAR (15),
4> CONSTRAINT pkey PRIMARY KEY (FirstName, LastName)  ##建立主键
5> ); 

7、分区的方法

$ sqlcmd
1> PARTITION TABLE Reservation ON COLUMN FlightID;
2> PARTITION TABLE Customer ON COLUMN CustomerID;

8、查看数据库schema

You can also use the sqlcmd show directive to see a list of the current database tables and all procedures.
For additional details about the schema, execute the @SystemCatalog system procedure. Use any of the
following arguments to @SystemCatalog to obtain details about a component of the database schema:
• TABLES
• COLUMNS
• INDEXINFO
• PRIMARYKEYS
• PROCEDURES
• PROCEDURECOLUMNS
For example:
$ sqlcmd
1> SHOW TABLES;
2> SHOW PROCEDURES;
3> EXEC @SystemCatalog COLUMNS;

9、修改表结构

Modifying Tables
After creating a table in a database with CREATE TABLE, you can use ALTER TABLE to make the following types of table changes:


• Altering a Table Column's Data Definition
• Adding and Dropping Table Columns
• Adding and Dropping Table Constraints
To drop an entire table, use the DROP TABLE DDL statement.

9.1 修改列属性

You can make the following types of alterations to a table column's data definition:

1> ALTER TABLE Airport ALTER COLUMN Name VARCHAR(25);
2> ALTER TABLE Airport ALTER COLUMN Country SET DEFAULT 'USA';

去除默认值的方法

To remove a default, redefine the column data definition, for example:


ALTER TABLE Airport ALTER COLUMN Country VARCHAR(15);


3> ALTER TABLE Airport ALTER COLUMN Name SET NOT NULL;
The examples

9.2 删除或者添加列

$ sqlcmd
1> ALTER TABLE Airport ADD COLUMN AirportCode VARCHAR(3)
2> BEFORE AirportID;
3> ALTER TABLE Airport DROP COLUMN AirportID;

Note, we recommend that you not define the UNIQUE or ASSUMEUNIQUE constraint directly on a column definition when adding a column or creating a table. If you do, the constraint has no name so you cannot drop the constraint without dropping the entire column. Instead, we recommend you apply UNIQUE or ASSUMEUNIQUE by adding the constraint (see Section 4.6.3.3, “Adding and Dropping Table Constraints”) or by adding an index with the constraint (see Section 4.6.4, “Adding and Dropping Indexes”). Defining these constraints this way names the constraint, which makes it easier to drop later if necessary.

建议在建表的时候不要立即加上唯一索引unique,因为这样唯一索引没有名称,然后你必须将列删除了才可以去掉索引。

BEFORE column-name — Table columns cannot be reordered but the BEFORE clause allows you to place a new column in a specific position with respect to the existing columns of the table. 

before 关键字可以指定添加列的位置。

9.3添加或者删除约束性

Adding and Dropping Table Constraints
You cannot alter a table constraint but you can add and drop table constraints. If the table contains existing data, you cannot add UNIQUE, ASSUMEUNIQUE, or PRIMARY KEY constraints.

如果表有数据,将无法添加约束。


$ sqlcmd
1> ALTER TABLE Airport ADD CONSTRAINT
2> uniquecode UNIQUE (Airportcode);

删除约束的方法。

ALTER TABLE Airport DROP CONSTRAINT uniquecode;


3> ALTER TABLE Airport ADD PRIMARY KEY (AirportCode);
The examples

删除主键的方法

ALTER TABLE Airport DROP PRIMARY KEY;

9.4建立唯一索引

$ sqlcmd
1> CREATE INDEX flightTimeIdx ON Flight (departtime);


The CREATE INDEX statement explicitly creates an index. VoltDB creates an index implicitly when you specify the table constraints UNIQUE, PRIMARY KEY, or ASSUMEUNIQUE. 

当使用关键字UNIQUE和PRIMARY KEY的时候,默认是建了索引的。

9.5 为存储过程添加或者删除分区

$ sqlcmd
1> PARTITION TABLE Airport ON COLUMN Name;


2> CREATE PROCEDURE FindAirportCodeByName AS
3> SELECT TOP 1 AirportCode FROM Airport WHERE Name=?;


4> PARTITION PROCEDURE FindAirportCodeByName
5> ON TABLE Airport COLUMN Name;

删除存储过程上的分区的方法是删除后,重新建。

1> DROP PROCEDURE FindAirportCodeByName;
2> CREATE PROCEDURE FindAirportCodeByName AS
3> SELECT TOP 1 AirportCode FROM Airport WHERE Name=?;


6> CREATE PROCEDURE FindAirportCodeByCity AS
7> SELECT TOP 1 AirportCode FROM Airport WHERE City=?;


The stored procedures are tested with the following sqlcmd directives:


$ sqlcmd
1> exec FindAirportCodeByName 'Logan Airport';
2> exec FindAirportCodeByCity 'Boston';

9.6 为表添加或者删除分区

注意为表添加分区必须表里面没有数据。

Before executing the following steps, save the existing schema so you can easily re-create the table. The VoltDB Management Center provides a view of the existing database schema DDL source, which you can download and save.


$ sqlcmd
1> DROP PROCEDURE FindAirportCodeByName;
2> DROP PROCEDURE FindAirportCodeByCity;
3> DROP TABLE Airport IF EXISTS CASCADE;
4> CREATE TABLE AIRPORT (
5> AIRPORTCODE varchar(3) NOT NULL,
6> NAME varchar(25),
7> CITY varchar(25),
8> COUNTRY varchar(15) DEFAULT 'USA',
9> CONSTRAINT UNIQUECODE UNIQUE (AIRPORTCODE),
10> PRIMARY KEY (AIRPORTCODE)
11> );
12> CREATE PROCEDURE FindAirportCodeByName AS
13> SELECT TOP 1 AirportCode FROM Airport WHERE Name=?;
14> CREATE PROCEDURE FindAirportCodeByCity AS
15> SELECT TOP 1 AirportCode FROM Airport WHERE City=?;
The example is described as follows:
Drop all stored procedures that reference the table. You cannot drop a table if stored procedures reference it. Drop the table. Options you may include are:


• IF EXISTS — Use the IF EXISTS option to avoid command errors if the named table is already removed.


• CASCADE — A table cannot be removed if it has index or view references. You can remove
the references explicitly first or use the CASCADE option to have VoltDB remove the references along with the table.


Re-create the table. By default, a newly created table is a replicated table.
Re-create the stored procedures that access the table. If the stored procedure is implemented with Java and changes are required, modify and reload the code before re-creating the stored procedures.
 

9.6删除存储过程

1. Drop the stored procedure from the database.
$ sqlcmd
1> DROP PROCEDURE GetAirport;
2. Remove the code from the database. If the procedure is implemented with Java, use the sqlcmd remove
classes directive to remove the procedure's class from the database.
2> remove classes myapp.procedures.GetAirport;

10、VoltTable数据结构类型

包含,rows ,每个row里面的列是按照 列名和值 的形式存储,并且列的数目与查询条件有关。

public final SQLStmt getressql = new SQLStmt(
"SELECT r.ReserveID, c.FirstName, c.LastName " +
"FROM Reservation AS r, Customer AS c " +
"WHERE r.FlightID=? AND r.CustomerID=c.CustomerID;");

如上查询返回的数据结构为:

 

11、Install 存储过程到数据库

步骤1 编译并且加载class文件到数据库

Compiling, Packaging, and Loading Stored Procedures

The VoltDB stored procedures are written as Java classes, so you compile them using the Java compiler.


Anytime you update your stored procedure code, remember to recompile, package, and reload it into the database using the following steps:


$ javac -classpath "./:/opt/voltdb/voltdb/*" \
-d ./obj \
*.java


$ jar cvf myproc.jar -C obj .


$ sqlcmd
1> load classes myproc.jar;
2> show classes;

步骤二、根据加载的class 声明存储过程。

The following DDL statements declare five stored procedures, identifying them by their class name:
$ sqlcmd
1> CREATE PROCEDURE FROM CLASS fadvisor.procedures.LookupFlight;
2> CREATE PROCEDURE FROM CLASS fadvisor.procedures.HowManySeats;
3> CREATE PROCEDURE FROM CLASS fadvisor.procedures.MakeReservation;

11.1关于存储过程的分区原理

假如存储过程是建立在FlightId上面。

PARTITION PROCEDURE MakeReservation ON TABLE Reservation COLUMN FlightID;

 

The PARTITION PROCEDURE statement assumes that the partitioning column value is also the first parameter to the stored procedure. Suppose you wish to partition a stored procedure on the third parameter such as the procedure GetCustomerDetails(), where the third parameter is a customer_id. You must specify the partitioning parameter using the PARAMETER clause and an index for the parameter position.

The index is zero-based so the third parameter would be "2" and the PARTITION PROCEDURE statement would be as follows:

对于存储过程分区来说,默认认为分表的列也是存储过程当中第一个参数。如果不是这样子的话,需要在建存储过程的声明的时候,需要加上parameter 参数。


PARTITION PROCEDURE GetCustomerDetails
ON TABLE Customer COLUMN CustomerID
PARAMETER 2;

 

12、分区存储过程查询注意事项 Queries in Single-Partitioned Stored Procedures

the shared partitioning column.
• The following WHERE constraint is also used: WHERE partitioned-table. identifier=?
In this example, WHERE RESERVATION.FLIGHTID=?


For example, the RESERVATION table can be joined with the FLIGHT table (which is replicated).

However,the RESERVATION table cannot be joined with the CUSTOMER table in a single-partitioned stored procedure because the two tables use different partitioning columns. (CUSTOMER is partitioned on the CUSTOMERID column.)


The following are examples of invalid SQL queries for a single-partitioned stored procedure partitioned
on FLIGHTID:


• INVALID:

SELECT * FROM reservation WHERE reservationid=?
The RESERVATION table is being constrained by a column (RESERVATIONID)

which is not the partitioning column. 

查询没有放在partition列上面。


• INVALID:

SELECT c.lastname FROM reservation AS r, customer AS c WHERE
r.flightid=? AND c.customerid = r.customerid


The correct partitioning column is being used in the WHERE clause, but the tables are being joined on a different column. As a result, not all CUSTOMER rows are available to the stored procedure since the CUSTOMER table is partitioned on a different column than RESERVATION.

总结,两表联合查询条件,要么是有一个表示replicated,要么是两个表示建立在同一partition列

12、连接voltdb数据库

12.1连接一个server


The first task for the calling program is to create a connection to the VoltDB database. You do this with the following steps:


org.voltdb.client.Client client = null;


ClientConfig config = null;


try {


config = new ClientConfig("username","password");//可以不需要参数


client = ClientFactory.createClient(config);


client.createConnection("myserver.xyz.net");//server的地址可以是域名或者IP

//也可以加上端口号 client.createConnection("myserver.xyz.net",21211);


} catch (java.io.IOException e) {
e.printStackTrace();
System.exit(-1);
}


Define the configuration for your connections. In its simplest form, the ClientConfig class specifies the username and password to use. It is not absolutely necessary to create a client configuration object. For example, if security is not enabled (and therefore a username and password are not needed) a configuration object is not required. But it is a good practice to define the client configuration to ensure the same credentials are used for all connections against a single client. It is also possible to define additional characteristics of the client connections as part of the configuration, such as thetimeout period for procedure invocations or a status listener. (See Section 6.5, “Handling Errors”.)

 

12.2 多个server

You can create the connection to any of the nodes in the database cluster and your stored procedure will be routed appropriately. In fact, you can create connections to multiple nodes on the server and your subsequent requests will be distributed to the various connections.

这个貌似,可以路由存储过程执行请求,减少瓶颈。

Multiple connections distribute the stored procedure requests around the cluster, avoiding a bottleneck
where all requests are queued through a single host. This is particularly important when using asynchronous
procedure calls or multiple clients.

try {
client = ClientFactory.createClient();
client.createConnection("server1.xyz.net");
client.createConnection("server2.xyz.net");
client.createConnection("server3.xyz.net");
} catch (java.io.IOException e) {
e.printStackTrace();
System.exit(-1);
}

 

© 著作权归作者所有

共有 人打赏支持
Oscarfff
粉丝 73
博文 815
码字总数 96913
作品 0
崇明
后端工程师
私信 提问
MySQL/HandlerSocket和VoltDB:NoSQL的竞争者

一般认为NoSQL数据库在性能方面要优于传统的SQL数据库。但是有两个SQL的解决方案宣布:对于大型系统的高可扩展性需求,SQL仍然是可行的解决方案!这两个SQL解决方案分别是MySQL加NoSQL层插件...

wkh
2014/01/07
0
0
VoltDB 3.0 发布,基于内存的数据库系统

基于内存的数据库系统 VoltDB 宣布 3.0 正式版发布,与之前的版本比较,3.0 在性能上又有了很大的飞跃,请看下图: VoltDB 3.0 每秒钟可执行数以万计的 SQL 查询。 此外 3.0 在简化开发上也做...

oschina
2013/01/26
3K
3
VoltDB 简介和入门

简介 过去几年来,出现了一种称为 NoSQL 的新型数据库管理系统。设计这些数据存储是为了克服在扩展传统关系数据库来处理一些应用程序时必须处理的数据负载类型的难题,比如说 Amazon。这种可...

IBMdW
2013/03/04
10K
0
VoltDB 真的有那么强的处理能力吗??

VoltDB是Postgres和Ingres联合创始人Mike Stonebraker领导开发的下一代开源数据库管理系统。它能在现有的廉价服务器集群上实现每秒数百万次数据处理。VoltDB大幅降低了服务器资源 开销,单节...

红薯
2011/03/01
3.1K
3
voltDB新手请教

@CrS 你好,想跟你请教个问题: 我看了您空间的一片博文,是关于voltDB的,我现在是初试voltDB,想请教您,我现在想将voltDB与我的web项目整合,我现在不知道怎么去弄了,所以想请教您。还望您...

颜海丰
2013/03/27
818
0

没有更多内容

加载失败,请刷新页面

加载更多

崛起于Springboot2.X之通讯WebSocket(40)

技术简介:Springboot2.0.3+freemaker+websocket 1、添加pom依赖 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-bo......

木九天
11分钟前
0
0
Java常用四大线程池用法以及ThreadPoolExecutor详解

为什么用线程池? 1.创建/销毁线程伴随着系统开销,过于频繁的创建/销毁线程,会很大程度上影响处-理效率 2.线程并发数量过多,抢占系统资源从而导致阻塞 3.对线程进行一些简单的管理 在Java中...

孟飞阳
13分钟前
0
0
Netty+Websocket 实现一个简易聊天室

后台代码 /** * 服务端 */public class ChatServer {public static void main(String[] args) throws Exception {int port=8080; //服务端默认端口new ChatServer().bind...

这很耳东先生
15分钟前
1
0
一个本科学生对Linux的认知

我是一名大三的普通一本大学的软件工程的一名学生,学校开设了一些关于系统开发的课程,纸上得来终觉浅,学校的课程课时较短,想要在56个课时之内学会一些公司需要的技能,无疑是纸上谈兵,一...

linuxprobe16
17分钟前
0
0
如何选择开源许可证?

如何为代码选择开源许可证,这是一个问题。 世界上的开源许可证,大概有上百种。很少有人搞得清楚它们的区别。即使在最流行的六种----GPL、BSD、MIT、Mozilla、Apache和LGPL----之中做选择,...

吴伟祥
18分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部