categoricaldata.github.io/csv.html at master · CategoricalData/categoricaldata.github.io · GitHub

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
<!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> |
<a href="mailto:info@conexus.com">Contact</a>
<br><br>
<hr>
<hr >
					<h2>CSV Import/Export</h2>

<p>This example is recommended for anyone dealing with CSV data and is built-in to the IDE as QuickCSV.
It imports a set of cloud-based CSV files about land and water animals into CQL,
demonstrates basic CQL operations, and then exports the data to a local CSV file.
We use SQL types (String, Integer, etc).  </p>

<p> Our land and water schemas share a common overlap schema about amphibians,
 which we express using an import statement. </p>

<pre>
schema AmphibianSchema = literal : sql {
	entities
		Amphibian Animal
	foreign_keys
		toAnimal : Amphibian -> Animal
	attributes
	     species : Animal -> String }
</pre>
<p>The schemas have data integrity constraints stating that every amphibian is an animal in one way.
See <a href="eqs.php">the path equality example</a> for details.
<pre>
schema LandSchema = literal : sql {
	imports
		AmphibianSchema
	entities
		LandAnimal
	foreign_keys
		isA : Amphibian  -> LandAnimal
		isA : LandAnimal -> Animal
	path_equations
		Amphibian.isA.isA = Amphibian.toAnimal }
</pre>
<pre>
schema WaterSchema = literal : sql {
	imports
		AmphibianSchema
	entities
		WaterAnimal
	foreign_keys
		isA : Amphibian  -> WaterAnimal
		isA : WaterAnimal -> Animal
	path_equations
		Amphibian.isA.isA = Amphibian.toAnimal     }
</pre>
<p> Next, we import <a href="categoricaldata.net/demo/">CSV files</a> over the internet.
CQL expects one file per entity, with one column
per foreign key or attribute.
<img src="images/examples/csv1.png" alt="csv1" width="700" >
</p>
<p>
There are many options, such as providing a column to column
name mapping during import, or to generate missing fields, etc; for details, see the built-in
CSV example.  </p>
<pre>
instance LandInstance = import_csv "http://categoricaldata.net/demo/LandDB/" : LandSchema

instance WaterInstance = import_csv "http://categoricaldata.net/demo/WaterDB/" : WaterSchema
</pre>
<img src="images/examples/csv2.png" alt="csv2" width="700" >
<p> We conclude by exporting our data to a set of CSV files locally.
First we clear out a folder, and the we export to the given folder. </p>
<pre>
command clear = exec_cmdline { "rm -rf exportedLand" }

command exportLand = export_csv_instance LandInstance "exportedLand/"
</pre>
<p>
It's recommended to use a trailing slashes to indicate a directory,
 but the string is actually used as a prefix in a URL. We can import the
local CSV data like so:</p>
<pre>
instance backAgain = import_csv "exportedLand/" : LandSchema
</pre>

	</div><!--close main-->

</body>
</html>