Transcript Lecture8_DatabaseImplementationx

Lecture 8
Database Implementation
Jeffery S. Horsburgh
Hydroinformatics
Fall 2012
This work was funded by National Science
Foundation Grant EPS 1135482
Objectives
• Examine the features of a Relational Database
Management System (RDBMS)
• Create a physical implementation of an
Observations Data Model (ODM) database
within a RDBMS
• Load observational data into an ODM
database
2 of 26
What We have Talked About
• Data management and the data life cycle
• Metadata
– Information needed to describe observational
data
• Data models and data model design
– How do you structure the data and metadata for
storage and retrieval
• Google Fusion Tables and Excel Tools
3 of 26
Where We are Going
• Database implementation – TODAY!!
– How do you physically implement a data model
and load data
• Using Structured Query Language (SQL)
– Using code to slice and dice your data
• Accessing databases from client software
– Getting data from a database into R
4 of 26
Relational Database Management Systems
• Robust software for managing data
• Data stored in tables that may be related
• Based on Structured Query Language (SQL)
– Adopted by the American National Standards
Institute (ANSI) and the International Standards
Organization (ISO) as the standard data access
language
5 of 26
File Versus Server-Based RDBMS
• File-based
– Everything contained within a single file
– Generally good for single user, desktop applications
– Examples: SQLite, Microsoft Access
• Server-based
– A database “server” manages databases and all
transactions
– Good for multiple, simultaneous connections and
transactions
– Examples: Oracle, Microsoft SQL Server, MySQL,
PostgreSQL
6 of 26
The Beauty of Server-Based RDBMS
Data
Database
7 of 26
Authentication and Access Control
Yay!
I want some
Database ServerDo I know you?Do I recognize
data!
Are you your IP address?
With RDBMS
authorized?
Firewall
Simultaneous Data Users
Server-Based RDBMS – “Granules”
• Database Server
• Databases
• Tables
• Records
8 of 26
DB1
DB2
DB3
...
DBn
Site Variable
Date
Value
1 Temperature 8/2/2007 14:00 12.4
1 Temperature 8/2/2007 14:30 12.7
1 Temperature 8/2/2007 15:00 13.1
1
Temperature
8/2/2007 14:00 12.4
Physical Implementation of a Data
Model within a RDBMS
1.
2.
3.
4.
Create a new database
Create tables for entities
Define attributes and data types
Create relationships and define their
properties
5. Define constraints
All of this can be scripted/automated using SQL
9 of 26
DEMO: Microsoft SQL Server and
SQL Server Management Studio
10 of 26
CUAHSI HIS – Sharing Hydrologic Data
The CUAHSI Hydrologic Information System (HIS) is an internet based system
to support the sharing of hydrologic data. It is comprised of hydrologic
databases and servers connected through web services as well as software for
data publication, discovery and access.
HIS Central
Data Discovery and
Integration platform
HydroServer
Data Publication
platform
Like web
servers
11 of 26
Data Services
Water Data Services
Spatial Data Services
Like HTML
Like search portals
Google, Yahoo, Bing
HydroDesktop
Data Synthesis and
Research platform
Like browsers
HydroServer Software Stack
• Observations Data Model (ODM)
– Relational database schema for hydrologic
observations (store data)
• ODM Software Utilities
– ODM Data Loader (load table-based data)
– ODM Streaming Data Loader (load streaming data)
– ODM Tools (visualize and manage data)
• WaterOneFlow web services (publish data)
12 of 26
ILO-4
Database Implementation and Loading Data
• GOAL: Organize and load observational data
for a continuous water quality monitoring site
in the Little Bear River to an ODM database on
the class server.
13 of 26
The Data You Will be Using
•
7 water quality and streamflow
monitoring sites
–
–
–
–
–
–
•
4 weather stations
–
–
–
–
–
–
–
•
Temperature
Dissolved Oxygen
pH
Specific Conductance
Turbidity
Water level/discharge
Temperature
Relative Humidity
Solar radiation
Precipitation
Barometric Pressure
Wind speed and direction
Soil moisture
Spread spectrum radio telemetry
network
14 of 26
hydroserver.uwrl.usu.edu
ODM
15 of 26
Authentication and Access Control
The Class HydroServer
Firewall
DEMO: Create a blank ODM
database in Microsoft SQL Server
1. Download the ODM blank SQL Server schema
2. Extract the SQL Server blank schema
database to the SQL Server data directory
3. Attach database to the SQL Server database
engine
16 of 26
Authentication in SQL Server
• Windows Authentication
– Uses a Windows account to
authenticate a user
– Users must have an
account on the server
• SQL Server Authentication
– Specific to SQL Server
– Enables you to create
accounts for SQL Server
without giving access to
the rest of the server
17 of 26
Authorization in SQL Server
• Assigning specific permissions to specific users
for specific database objects
18 of 26
DEMO: Creating Database Users
and Assigning Database Permissions
in SQL Server
Load Data into the Database
1. Load metadata using the ODM Data Loader
2. Load observations using the ODM Streaming
Data Loader
• Advantages of using the ODM Data Loaders
– Designed specifically for ODM
– Validate data against the business logic of ODM
– Protect the consistency of data and avoid errors
20 of 26
ODM Data Loading Strategy
21 of 26
ODM Data Loader
• Loading data to any table in ODM – e.g., Sites,
Variables, Methods, etc.
• Loading Data Values in serial format
– Multiple sites and variables in one file
– All data values in a single column
22 of 26
Site
1
1
1
…
1
1
1
…
2
2
2
Variable
Temperature
Temperature
Temperature
…
Dissolved Oxygen
Dissolved Oxygen
Dissolved Oxygen
…
Specific Conductance
Specific Conductance
Specific Conductance
Date
8/2/2007 14:00
8/2/2007 14:30
8/2/2007 15:00
…
8/2/2007 14:00
8/2/2007 14:30
8/2/2007 15:00
…
8/2/2007 14:00
8/2/2007 14:30
8/2/2007 15:00
Value
12.4
12.7
13.1
…
9.78
9.70
9.56
…
351.7
350
351.2
ODM Streaming Data Loader
• Focused on loading data values
• Crosstab data
– Single site, multiple variables
– Date in one column, each variable in one column
– Datalogger files from field sensors
23 of 26
Date
Variable_1 Variable_2
8/2/2007 14:00
13.01298
1.155137
8/2/2007 14:30
13.222
1.15333
8/2/2007 15:00
13.19934
1.153291
8/2/2007 15:30
13.19094
1.153561
8/2/2007 16:00
13.17751
1.16075
…
…
…
…
…
…
…
…
…
…
Variable_n
9.391667
9.586666
9.75
9.78
9.703334
…
Visualize Data Using ODM Tools
1. Connect to the database using a client
software application
2. Visualize and manage data using the
software tools
24 of 26
DEMO: ODM Tools
25 of 26
What You Will Do for the ILO
1. In your personal ODM database on the server
a. Load Site, Variable, Method, and Source metadata
using the ODM Data Loader
b. Load sensor observations using the ODM Streaming
Data Loader
2. Visualize and explore the data using ODM Tools
3. Evaluate ODM as a data management tool for
sensor data from the Little Bear River
experimental watershed
26 of 26