MySQL Plugin

Users can import pre-compiled MySQL plug-ins (in the DolphinDB installation package or under the bin directory) with the following command in DolphinDB:

In Linux:

loadPlugin("/path/to/plugins/mysql/PluginMySQL.txt")

In Windows:

loadPlugin("C:/path/to/mysql/PluginMySQL.txt")

Note that you must load the plugin with an absolute path and replace "\" with "\\" or "/".

Syntax

mysql::connect(host, port, user, password, db)

Parameters

• host: a string indicating the address of the MySQL server.

• port: an int indicating the port of the MySQL server.

• user: a string indicating the user name of the MySQL server.

• password: a string indicating the password of the MySQL server.

• db: a string indicating the database name.

Details

Create a connection to the MySQL server. Return a handle of MySQL connection, which will be used to access the MySQL server later.

Example

conn = mysql::connect(`localhost, 3306, `root, `root, `DolphinDB)

Syntax

mysql::showTables(connection)

Parameters

• connection: a MySQL connection handle created with mysql::connect.

Details

List all table names in a MySql database specified in mysql::connect.

Examples

conn = mysql::connect(`localhost, 3306, `root, `root, `DolphinDB)
mysql::showTables(conn)

output:
  Tables_in_DolphinDB
  -------------------
  US

Syntax

mysql::extractSchema(connection, tableName)

Parameters

• connection: a MySQL connection handle created with mysql::connect.

• tableName: a string indicating the name of a table in MySQL server.

Details Generate the schema of a table.

Examples

conn = mysql::connect(`localhost, 3306, `root, `root, `DolphinDB)
mysql::extractSchema(conn, `US)

output:
        name    type        DolphinDBType
        PERMNO  INT         int(11)
        date    DATE        date
        SHRCD   INT         int(11)
        TICKER  SYMBOL      varchar(10)
        ...
        PRC     DOUBLE      double

Syntax

mysql::load(connection, table_or_query, [schema], [startRow], [rowNum])

Parameters

• connection: a MySQL connection handle created with mysql::connect.

• table_or_query: a string indicating the name of a MySQL server table or a MySQL query.

• schema: a table with names and data types of columns. If we need to change the data type of a column that is automatically determined by the system, the schema table needs to be modified and used as an argument.

• startRow: an integer indicating the index of the starting row to read. If unspecified, read from the first row. If 'table_or_query' is a SQL query, then 'startRow' should be unspecified.

• rowNum: an integer indicating the number of rows to read. If unspecified, read to the last row. If 'table_or_query' is a SQL query, then 'rowNum' should be unspecified.

Note: If 'table_or_query' is a SQL query, use 'LIMIT' in SQL query to specify 'startRow' and 'rowNum'.

• allowEmptyTable: a Boolean indicating whether to allow importing an empty table from MySQL. The default value is false.

Details

Load a MySQL table or SQL query result into a DolphinDB in-memory table.

For details about supported data types as well as data conversion rules, please refer to the section of Data Types below.

Examples

conn = mysql::connect(`192.168.1.18, 3306, `root, `root, `DolphinDB)
tb = mysql::load(conn, `US,,0,123456)
select count(*) from tb

conn = mysql::connect(`127.0.0.1, 3306, `root, `root, `DolphinDB)
tb = mysql::load(conn, "SELECT PERMNO FROM US LIMIT 123456")
select count(*) from tb

mysql::load(conn, "SELECT now(6)");

Syntax

mysql::loadEx(connection, dbHandle,tableName,partitionColumns,table_or_query,[schema],[startRow],[rowNum],[transform])

Parameters

• connection: a MySQL connection handle created with mysql::connect.

• dbHandle and tableName: If the input data is to be saved into a distributed database, the database handle and table name should be specified.

• partitionColumns: a string scalar/vector indicating partitioning column(s).

• table_or_query: a string indicating the name of a MySQL server table or a MySQL query.

• schema: a table with names and data types of columns. If we need to change the data type of a column that is automatically determined by the system, the schema table needs to be modified and used as an argument.

• startRow: an integer indicating the index of the starting row to read. If unspecified, read from the first row. If 'table_or_query' is a SQL query, then 'startRow' should unspecified.

• rowNum: an integer indicating the number of rows to read. If unspecified, read to the last row. If 'table_or_query' is a SQL query, then 'rowNum' should unspecified.

Note: If 'table_or_query' is a SQL query, use 'LIMIT' in SQL query to specify 'startRow' and 'rowNum'.

• transform: apply certain transformation on a MySQL table or query before importing into DolphinDB database.

Details

Load a MySQL table as a distributed table. The result is a table object with loaded metadata.

For details about supported data types as well as data conversion rules, please refer to the Data Types section.

Examples

• Load data as a partitioned table on disk.

  Load the entire table

  dbPath = "C:/..."
  db = database(dbPath, RANGE, 0 500 1000)
  mysql::loadEx(conn, db,`tb, `PERMNO, `US)
  tb = loadTable(dbPath, `tb)

  Load via SQL statement

  dbPath = "C:/..."
  db = database(dbPath, RANGE, 0 500 1000)
  mysql::loadEx(conn, db,`tb, `PERMNO, "SELECT * FROM US LIMIT 1000");
  tb = loadTable(dbPath, `tb)

• Load data as an in-memory partitioned table

  Load the entire table

  db = database("", RANGE, 0 50000 10000)
  tb = mysql::loadEx(conn, db,`tb, `PERMNO, `US)

  Load via SQL statement

  db = database("", RANGE, 0 50000 10000)
  tb = mysql::loadEx(conn, db,`tb, `PERMNO, "SELECT * FROM US LIMIT 100");

• Load data as a DFS partitioned table

  Load the entire table

  db = database("dfs://US", RANGE, 0 50000 10000)
  mysql::loadEx(conn, db,`tb, `PERMNO, `US)
  tb = loadTable("dfs://US", `tb)

  Load via SQL statement

  db = database("dfs://US", RANGE, 0 50000 10000)
  mysql::loadEx(conn, db,`tb, `PERMNO, "SELECT * FROM US LIMIT 1000");
  tb = loadTable("dfs://US", `tb)

  Load and transform data into a DFS partitioned table

  db = database("dfs://US", RANGE, 0 50000 10000)
  def replaceTable(mutable t){
  	return t.replaceColumn!(`svalue,t[`savlue]-1)
  }
  t=mysql::loadEx(conn, db, "",`stockid, 'select  * from US where stockid<=1000000',,,,replaceTable)
  

• The numeric types in DolphinDB are all signed types. To prevent overflow, all unsigned types are converted to high-order signed types. For example, unsigned CHAR is converted to signed SHORT, unsigned SHORT is converted to signed INT, etc. 64-bit unsigned types are not supported.

• 'unsigned long long' is not supported in DolphinDB, you can specify schema and use DOUBLE or FLOAT if needed.

• The smallest value of each integral type in DolphinDB is NULL value, e.g. -128 for CHAR, -32,768 for SHORT, -2,147,483,648 for INT and -9,223,372,036,854,775,808 for LONG all mean NULL values in each type respectively.

Note: IEEE754 floating-point types are all signed numbers.

• All floating-point types can be converted to numeric types (BOOL, CHAR, SHORT, INT, LONG, FLOAT, DOUBLE) in DolphinDB.

• All data types above can be converted to temperal data types in DolphinDB (DATE, MONTH, TIME, MINUTE, SECOND, DATETIME, TIMESTAMP, NANOTIME, NANOTIMESTAMP).

• char and varchar types of length less or equal to 10 will be converted to SYMBOL type in DolphinDB. Other string types will be converted to STRING type in DolphinDB.

• string type will be converted to STRING or SYMBOL type in DolphinDB.

• enum type will be converted to SYMBOL type in DolphinDB.

• CPU: i7-7700 3.60GHZ

• Hard disk: SSD, read speed 460~500MB/s.

• US stocks daily data from 1990 to 2016 with 22 fields and 50,591,907 rows. Total size is 6.5GB.

160.5 seconds