-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathjdbc.html
150 lines (119 loc) · 5.36 KB
/
jdbc.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
<!DOCTYPE HTML>
<html lang="en">
<head>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-CD4ENCFV58"></script>
<!-- Google tag (gtag.js) -->
<script async src="https://www.googletagmanager.com/gtag/js?id=G-64DRFX06T1"></script>
<script >
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'G-64DRFX06T1');
</script>
<title>CQL</title>
<link rel="shortcut icon" href="../favicon.ico" >
<link rel="StyleSheet" href="css/nstyle.css" type="text/css" media="all" >
<meta charset="utf-8">
<meta name="keywords" content="CQL,SQL,Data Integration, Data Migration, Category Theory, ETL" >
<meta name="description" content="Conexus CQL" >
<meta name="keywords" content="CQL, functorial, category theory, data integration, data migration, categorical databases, SQL, categorical query language" >
</head>
<body>
<div id="content">
<h1>Categorical Databases<img src="logo.png" height="32" style="float: right;" alt="logo" ></h1>
<a href="https://categoricaldata.net">Home</a> |
<a href="download.html">Download</a> |
<a href="examples.html">Getting Started</a> |
<a href="help/index.html" target="_blank">Manual</a> |
<a href="https://github.com/CategoricalData/CQL/wiki" target="_blank">Wiki</a> |
<a href="papers.html">Papers</a> |
<a href="screens.html">Screen Shots</a> |
<a href="https://github.com/categoricalData" target="_blank">Github</a> |
<a href="https://groups.google.com/forum/#!forum/categoricaldata" target="_blank">Google Group</a> |
<a href="https://conexus.com" target="_blank">Conexus</a> |
<br><br>
<hr>
<h2>JDBC Import/Export</h2>
<p>In practice, a data integration task requires importing and exporing data from many CSV files, SQL databases, etc., and CQL provides seamless import/export capabilities for CSV and for SQL via JDBC.
</p>
<p>This example (built in to the IDE with name JDBC) defines a source schema about employees, and populates an instance on this schema by querying a database using JDBC. To be self-contained, the example creates a sample in-memory SQL database to import from, but any SQL database accessible over JDBC can be used.
</p>
<p>We start by defining a source SQL schema for employees and populating it with sample data. We must provide the name of a JDBC driver (in this case, H2), along with a JDBC connection string indicating which database to connect to (in this case, db1). We then issue a series of SQL commands to create the sample input database. In practice, the initial sample data creation phase will be skipped because we will import real data.
</p>
<pre>pragma load = exec_jdbc "org.h2.Driver" "jdbc:h2:mem:db1" {
"CREATE TABLE Employee(
id INT PRIMARY KEY,
name VARCHAR(255),
manager INT,
worksIn INT
)"
"CREATE TABLE Department(
id INT PRIMARY KEY,
name VARCHAR(255),
secretary INT,
)"
"INSERT INTO Employee VALUES
(101, 'Alan', 103, 10),
(102, 'Camille', 102, 2),
(103, 'Andrey', 103, 10)"
"INSERT INTO Department VALUES
(10, 'Applied Math', 101),
(2, 'Pure Math', 102)"
} </pre>
<br>
<p>We import onto the following schema about employees and departments:
</p>
<pre>typeside Ty = literal {
java_types
String = "java.lang.String"
java_constants
String = "return input[0]"
}
schema S = literal : Ty {
entities
Employee
Department
foreign_keys
manager : Employee -> Employee
worksIn : Employee -> Department
secretary : Department -> Employee
attributes
first last : Employee -> String
name : Department -> String
}
</pre>
<br>
<p>To specify the import, we give a one-column SQL query for each entity (which defines the set of IDs for that entity), and a two column SQL query for each attribute and entity (which defines the function associated with each foreign key and attribute):
</p>
<pre>instance J = import_jdbc "org.h2.Driver" "jdbc:h2:mem:db1" : S {
Employee -> "SELECT id FROM Employee"
Department -> "SELECT id FROM Department"
manager -> "SELECT id, manager FROM Employee"
worksIn -> "SELECT id, worksIn FROM Employee"
secretary -> "SELECT id, secretary FROM Department"
first -> "SELECT id, name FROM Employee" //use name as first name
last -> "SELECT id, NULL FROM Employee" //use null as last name
name -> "SELECT id, name FROM Department"
}</pre>
<br>
<p>The result is displayed in the IDE:
</p>
<img src="images/examples/jdbc1.png" alt="jdbc1" width="700" >
<p>To export an instance to a SQL database over JDBC, we simply give a prefix, in this case Exported, and the tables are exported, in this case as ExportedEmployee and ExportedDepartment:
</p>
<pre>pragma store = export_jdbc_instance J "org.h2.Driver" "jdbc:h2:mem:db1" "Exported"
</pre>
<br >
<p>The exported data can be viewed in SQL, for example:</p>
<pre>SELECT * FROM ExportedEmployee;
ID id1, LAST null, FIRST Camille, MANAGER id1, WORKSIN id0
ID id3, LAST null, FIRST Alan, MANAGER id4, WORKSIN id2
ID id4, LAST null, FIRST Andrey, MANAGER id4, WORKSIN id2
SELECT * FROM ExportedDepartment;
ID id0, NAME Pure Math, SECRETARY id1
ID id2, NAME Applied Math, SECRETARY id3
</pre>
</div><!--close main-->
</body>
</html>