Loading a catalog

The sky arrives as files. Six lessons that load 9,096 stars, 88 constellations, the 743 lines that draw them and the 27 nakshatra junction stars, with Data Load Definitions you can run again whenever the files change. You finish with a full catalog and an admin page that loads it.

How Part 2 works

Turnout's users typed their data in. SkySutra's catalog comes from astronomers, as files, in astronomers' units: right ascension in hours, minutes and seconds, declination in degrees, minutes and seconds, star names with Greek letters. A Data Load Definition is APEX's way to say, once, how a file becomes rows: which column goes where, what to convert on the way in, what to look up, and what to do with a bad row.

Load order matters, and it's the order of the lessons: stars look up their constellation, lines look up their stars, nakshatras look up their junction star. A lookup can only find what's already loaded.

Before you start

You need Part 1 done: the 22 tables, the seed data, and sign-in working. Download this part's four files:

Lesson 2.1 — Choosing a catalog

Outcome: know what's in SkySutra's star file, where it came from, and why it stops at the stars you can see.

The question. There are catalogs with a billion stars. Why does SkySutra load 9,096?

The decision. Load the stars a person can see without a telescope, and nothing more.

The file is the Yale Bright Star Catalogue, 5th revised edition (Hoffleit and Warren, 1991): every star brighter than about magnitude 6.5, the limit of the eye on a dark night. It numbers 9,110 entries; 14 of them turned out not to be single stars (novae, a few clusters and galaxies) and have no position, so the file has 9,096 rows. Its numbers are the HR numbers from Lesson 1.1.

Open bright_stars.csv in a text editor and look at Aldebaran's row:

1457,Aldebaran,α Tauri,Tau,04h 35m 55.2s,+16° 30′ 33″,+0.063,-0.190,0.85,+1.54,K5+III,Rohini

None of it is in the shape SS_STARS wants. Right ascension is in hours, not degrees. Declination is degrees, minutes and seconds. Proper motion (how fast the star drifts across the sky over centuries) is in arcseconds a year; the table holds milliarcseconds. The constellation is Tau, the table wants an id. Lessons 2.2 and 2.3 handle each one.

Lesson 2.2 — Data Load Definitions

Outcome: create a Data Load Definition from a sample file, and know what each of its settings decides.

The question. We could write a PL/SQL procedure to read each file. What does a Data Load Definition give us instead?

The decision. Describe each file once, as a shared component; let APEX do the reading, converting and error handling; reuse the definition from a page, from PL/SQL (apex_data_loading.load_data) or both.

A definition has two halves:

A profile column whose name matches a table column is loaded into it. A column whose name doesn't match (like RA) is still read, and other columns can use it, but it isn't loaded anywhere.

1. Constellations

1. Shared Components › Data Load Definitions › Create. Keep From scratch, Next.
2. Name Constellations, Table Name SS_CONSTELLATIONS. Next.
3. Upload a File: drop in constellations.csv. Next.
4. On Map Columns, change File Encoding to Unicode UTF-8. The default is Western European ISO-8859-1, which would turn the ö in Boötes into two wrong characters.
5. The three columns map themselves: the file's headers match the table's columns. Tick Primary Key on the ABBR row. That makes this a Merge on the abbreviation: run it again and existing constellations are updated, not duplicated.
6. Look at Preview, then click Create Data Load (not "Create and Add Page": the page comes in Lesson 2.6, for all four files at once).

2. Stars, first pass

1. Create again: Name Stars, Table Name SS_STARS, sample file bright_stars.csv, File Encoding Unicode UTF-8.
2. Only BAYER, B_V and VEDIC_NAME map themselves; the others have different names in the file (hr, vmag, ra). Click Create Data Load anyway: the wizard keeps only mapped columns, and you'll add the rest properly in Lesson 2.3.
3. Open Stars. Set Loading Method to Merge later, once it has a primary key (Lesson 2.3). Set When on Error to Log Error into Collection, Collection Name STAR_LOAD_ERRORS. Apply Changes.

Why not Log into Error Log for stars? APEX refuses: "Log into Error Log not supported for Merge loading method nor Append loading method if a primary key column defined." A merging load reports its bad rows in a collection instead, and the load page shows them (Lesson 2.6).

Lesson 2.3 — Transformations on the way in

Outcome: turn the catalog's units into SkySutra's on the way in, without touching the file.

The question. RA arrives as 04h 35m 55.2s. Where does it become 68.98°?

The decision. In the profile, with three kinds of column: Data (read from the file, optionally tidied by a transformation), SQL Expression (computed from Data columns), and Lookup (an id found in another table).

1. Add the Data columns

Open Stars › Edit Data Profile. In the columns grid, use Add Data Column for each row below. Every column needs a Static ID (lower case with hyphens, like hr-number). Then Apply Changes.

Now go back to Stars and set Loading Method to Merge: HR_NUMBER is the key, so a second load updates each star in place. That matters: observations and lore point at stars by id, and a merge keeps the ids.

2. Compute degrees: SQL Expression columns

Add Column, Column Type SQL Expression, Data Type Number. The expression can use any Data column by name.

How the RA one works: regexp_substr(RA, '[0-9.]+', 1, n) picks the nth run of digits from 04h 35m 55.2s: 04, 35, 55.2. Hours plus minutes/60 plus seconds/3600 gives 4.5987 hours; times 15 (360° in 24 hours) gives 68.98°. Declination works the same way in degrees, with the sign taken from the front: -00° 30′ 11″ is negative even though its degrees are 0, which is why the sign is read from the text and not from the number.

3. Find the constellation: a Lookup column

The file says Tau; the table wants SS_CONSTELLATIONS.ID, and abbreviations are stored in capitals (TAU).

1. Edit the CONSTELLATION column. Set Transformation to To Upper Case. Apply Changes.
2. Add Column: Name CONSTELLATION_ID, Column Type Lookup, Data Type Number, Table SS_CONSTELLATIONS, Return Column ID, Table Column 1 ABBR, Data Column 1 CONSTELLATION.

A lookup that finds nothing returns null. constellation_id allows nulls, so a misspelled abbreviation would load a star with no constellation; the check at the end of this part counts them.

Lesson 2.4 — Constellations

Outcome: load the 743 lines that draw the constellations, and catch any line whose stars aren't in the catalog.

The question. A line is "from HR 1457 to HR 1409". The table wants two star ids. What if a number isn't in the catalog?

The decision. Three lookups per row, Replace as the loading method, and bad rows to an error log table.

1. Make the error table

In SQL Commands:

begin
    dbms_errlog.create_error_log('SS_CONSTELLATION_LINES');
end;

That creates ERR$_SS_CONSTELLATION_LINES: the same columns as the table, as text, plus ORA_ERR_MESG$ for the reason a row failed.

2. The definition

1. Create: Name Constellation Lines, Table Name SS_CONSTELLATION_LINES, sample file constellation_lines.csv, UTF-8. Only SEQ maps itself. Create Data Load.
2. Edit Data Profile, Add Data Column three times: CONSTELLATION (Varchar2, selector CONSTELLATION), FROM_HR (Number, FROM_HR), TO_HR (Number, TO_HR). Apply Changes.
3. Add Column three times, each a Lookup returning ID:

4. Back on the definition: Loading Method Replace; When on Error Log into Error Log; Error Logging Table ERR$_SS_CONSTELLATION_LINES. Apply Changes.

Why Replace here and Merge for stars

All three line columns are not null. If a lookup finds nothing, the insert fails, and the row lands in the error table with the reason, instead of stopping the load.

Lesson 2.5 — The 27 nakshatras

Outcome: point each nakshatra at its junction star, without touching anything else in the row.

The question. The 27 nakshatra rows already exist (Part 1's seed). The file only knows "Rohini, HR 1457". How does a load fill in one column?

The decision. Merge on SEQ, with only the columns the file can supply.

1. Create: Name Nakshatra Stars, Table Name SS_NAKSHATRAS, sample nakshatra_stars.csv, UTF-8. SEQ maps itself. Create Data Load.
2. Edit Data Profile: set Primary Key on SEQ, and Add Data Column HR_NUMBER (Number, selector HR_NUMBER). Apply Changes.
3. Add Column: JUNCTION_STAR_ID, Lookup on SS_STARS, return ID, HR_NUMBER = HR_NUMBER.
4. Loading Method Merge. Apply Changes.

A merge updates only the columns in the profile that exist in the table: here SEQ (unchanged) and JUNCTION_STAR_ID. Names, ranges and descriptions stay as the seed left them. The file's nakshatra and star columns are there for people reading it; nothing loads them.

Lesson 2.6 — Loads you can run again

Outcome: one page, for administrators only, that loads any of the four files and shows what happened.

The question. A developer can load files in the Builder. Who loads next year's corrected catalog?

The decision. A Data Loading page in the app, behind an Administrator role.

1. The role, and a wizard default to fix

1. Shared Components › Application Access Control › Add Role: Administrator, description "Loads and maintains the star catalog." Open it again and change Static ID to ADMINISTRATOR (Lesson 0.6: apex_acl.has_user_role upper-cases what it's given).
2. Add User Role Assignment: your own account, Administrator.
3. Shared Components › Authorization Schemes, open Administration Rights. The Create App wizard made it, and look at it: PL/SQL Function Returning Boolean, return true;. Every signed-in person passes. Change Scheme Type to Is In Role or Group, Type Application Role, Name(s) Administrator; Error Message "Only administrators can open this page." Apply Changes.

2. The page

1. Create Page › Data Loading. Page Number 20, Name Load the Catalog, Data Load Stars, Upload Data From File. Turn off the breadcrumb and the navigation entry. Create Page.
2. The wizard builds a whole page: a file drop zone (P20_FILE), a Preview of the first rows, a Load Data button, an Errors report on the STAR_LOAD_ERRORS collection, and two processes: Load Data (the Data Loading process) and Clear Cache.
3. In Page Designer, page 20: Security › Authorization Scheme Administration Rights; Page Alias LOAD.

3. One page, four files

1. Add a Select List P20_LOAD to the Data Source region, before the file: Label "Catalog file", Value Required on, Static Values:

The return values are the definitions' Static IDs. 2. Preview region: in its SQL, change p_static_id => 'stars' to p_static_id => :P20_LOAD, so the preview reads the file with the right profile. 3. Rename the process Load Data to Load stars, with Server-side Condition "Item = Value", P20_LOAD = stars. 4. Add three more Data Loading processes, the same but with Data Load Definition Constellations, Constellation Lines and Nakshatra Stars, File P20_FILE, When Button Pressed Load Data, and the matching condition value. Keep Clear Cache after them. 5. A classic report In the Catalog, to see the result:

select 'Constellations' as what, (select count(*) from ss_constellations) as loaded, 88 as expected from dual union all
select 'Stars', (select count(*) from ss_stars), 9096 from dual union all
select 'Constellation lines', (select count(*) from ss_constellation_lines), 743 from dual union all
select 'Nakshatras with a junction star', (select count(junction_star_id) from ss_nakshatras), 27 from dual

4. Load, in order

Run the app, open load (page 20), and for each file in turn: pick it, drop the file, check the preview, Load Data. Each load reports "Data loading finished: n rows processed with e errors."

Checkpoint

The catalog is in. Part 3 computes where each of those 9,096 stars is in your sky tonight.

Test it

1. "In the Catalog" shows 88, 9,096, 743 and 27.
2. Every star found its constellation, and no line failed:

select (select count(*) from ss_stars where constellation_id is null) as stars_without_constellation,
       (select count(*) from err$_ss_constellation_lines)              as failed_lines
  from dual;

Both are 0. 3. Aldebaran came through the conversions:

select hr_number, proper_name, bayer, ra_deg, dec_deg, pm_ra_mas, magnitude
  from ss_stars where hr_number = 1457;

RA 68.98, Dec 16.509167, proper motion 63, magnitude 0.85, Bayer α Tauri. (Lesson 0.3 used these numbers.) 4. The junction stars:

select n.seq, n.name, s.proper_name, s.bayer, s.magnitude
  from ss_nakshatras n join ss_stars s on s.id = n.junction_star_id
 order by n.seq;

27 rows: Ashwini is Sheratan (β Arietis), Rohini is Aldebaran. 5. Load Stars a second time. It finishes with no errors and the count stays at 9,096: a merge, not a copy.

What we found building it

• Bytes and characters. The first star load reported 20 errors: ORA-12899: value too large for column "SS_STARS"."BAYER" (actual: 22, maximum: 20). Names like "β Trianguli Australis" are 21 characters but 22 bytes, because a Greek letter takes two bytes in UTF-8. The schema now counts characters (alter session set nls_length_semantics = char at the top of skysutra_schema.sql, Lesson 1.8), and bayer is 30 long. If you built the tables before that fix: alter table ss_stars modify bayer varchar2(30 char);, and set the Stars profile's BAYER column Max Length to 30.
• Space. On the free apex.oracle.com tier, a load can fail with an error about space or quota. To see what's using it, run select segment_name, round(sum(bytes)/1024/1024, 1) as mb from user_segments group by segment_name order by 2 desc in SQL Commands, or open Administration › Manage Service › Request Storage › Detailed Tablespace Utilization Report. Drop sample tables and apps you no longer need, then run purge recyclebin; (dropped tables still count until you do), or ask for more under Manage Service › Request Storage.

Files

Exercises

• Add a line to a copy of constellation_lines.csv with an HR number that doesn't exist (TAU,99999,1457,99). Load it, then read ora_err_mesg$ in ERR$_SS_CONSTELLATION_LINES. Load the real file again to put things right.
• Use a Regular Expression Replace transformation to load PROPER_NAME with surrounding spaces removed, then explain why Trim does the same job more simply.
• Run the Stars definition from PL/SQL instead of the page: look up apex_data_loading.load_data and write the call (it needs a file as a BLOB or CLOB, and an APEX session).
• Stretch: count the stars in each constellation, and find which one has the most stars brighter than magnitude 4.

Review questions

Try each one before you open it. The graded quiz is at the end of the page.