Database Tutorial

Course Tutorial Site

Site Admin

Lesson #7: Aggregation

with 2 comments

This lesson focuses two things. First, how to aggregate numeric values in the database. Second, on how to get external data into the database in bulk.

It takes a considerable amount of data to make aggregation useful and to illustrate methods. Initial aggregation examples use only ordinal numbers but the lab uses transaction amounts linked to Video Store rentals.

The loading examples focus on importing Comma Separated Value (CSV) files into tables. These are often critical skills in any Extract, Transform, and Load (ETL) operation of a data warehouse or major data migration effort.

Transactions control an all or nothing import of values. They are the reason we learn how to write queries and other DML statements. Transactions are the process whereby we guarantee the ordering of events among several tables, and guarantee ACID compliant transactions. These elements linked to loading the data reinforce concepts introduced during Lesson #4 on Inserting Data, and extend the content of the Transaction Management post.

Aggregation
Learn how to use aggregation in SQL statements.
Oracle External Tables
Learn how to use Oracle External Tables to read the contents of CSV files.
MySQL CSV Upload
Learn how to use the LOAD LOCAL DATA clause to read a CSV file into an existing table.
MERGE Statement
Learn how to use the MERGE statement to insert a new record when it doesn’t exist, or update a row when the value already exists.
Import Transaction Units
Learn how to import CSV files into a database by leveraging the MERGE statement and ON DUPLICATE KEY clause. These let you insert a new record when it doesn’t exist, or update a row when the value already exists.

Written by michaelmclaughlin

October 8th, 2009 at 8:53 pm

Posted in

2 Responses to 'Lesson #7: Aggregation'

Subscribe to comments with RSS or TrackBack to 'Lesson #7: Aggregation'.

  1. I am unable to find any information for mounting a drive on a linux or unix system. I am told that mysql requires a manual drive mount before it can be used for logs and csv imports.

    Normally to mount in mac the command is,
    diskutil mount /dev/disk1s1
    Assuming you want disk 1 partition 1.

    Is there something I need to do to get the csv file into mysql

    spencer

    5 Nov 10 at 6:49 pm

  2. Grant the FILE permission to the user. The other suggestion that I assume you found on a web site was invalid.

    michaelmclaughlin

    22 Oct 14 at 2:09 am

Leave a Reply