Icon

This is the documentation for an older version of Qube. The latest version of the documentation can be found here: Qube

Versions Compared

Old Version 4

changes.mady.by.user John Burk

Saved on

compared with

New Version 5

changes.mady.by.user John Burk

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
mysql> SHOW CREATE TABLE pfx_dw.user_dim\G
*************************** 1. row ***************************
Table: user_dim
Create Table: CREATE TABLE `user_dim` (
`user_sk` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`user_sk`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> SELECT name FROM user_dim;
+---------------+
| name          |
+---------------+
| Administrator |
| bar | | foo | | foobar | | fubar | |
jburk | |foo perforce | | root | | shinya | +---------------+
No Format
mysql> SHOW CREATE|
TABLE pfx_dw.user_dim\G
*************************** 1. row ***************************
Table: user_dim
Create Table: CREATE TABLE `user_dim` (
`user_sk` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`user_sk`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> SELECT name FROM user_dim;
+---------------+
| name |
+---------------+
| Administrator |
| bar |
| foo |
| foobar |
| fubar |
| jburk |
| perforce |
| root |
| shinya | foobar        |
| fubar         |
| jburk         |
| perforce      |
| root          |
| shinya        |
+---------------+

 

 

Get a count of all jobs for a particular user:
Code Block
mysql> SELECT
	COUNT(*) 
FROM 
	job_fact AS fact
INNER JOIN
	user_dim AS user
ON
	fact.user_sk = user.user_sk
WHERE
	user.name = 'jburk'
;
+----------+
| COUNT(*) |
+----------+
| 5868     |
+----------+

The time dimension table

...

Code Block
mysql> SELECT 
	* 
FROM 
	time_dim 
ORDER BY 
	time_sk 
DESC LIMIT 1;
;
+------------+---------------------+------+------------+-----+------------+-------+------+
| time_sk    | date_time           | hour | date       | dow | month_name | month | year |
+------------+---------------------+------+------------+-----+------------+-------+------+
| 1392339600 | 2014-02-13 17:00:00 | 17   | 2014-02-13 | 5   | February   | 2     | 2014 |
+------------+---------------------+------+------------+-----+------------+-------+------+
1 row in set (0.00 sec)

mysql> SELECT 
	FROM_UNIXTIME(time_sk)
	, date_time 
FROM 
	time_dim 
ORDER BY 
	time_sk 
DESC LIMIT 1;
+------------------------+---------------------+
| FROM_UNIXTIME(time_sk) | date_time           |
+------------------------+---------------------+
| 2014-02-13 17:00:00    | 2014-02-13 17:00:00 |
+------------------------+---------------------+

...

Code Block
mysql> SELECT * FROM jobstatus_dim;
+--------------+------------+-------------+----------------+-------------+
| jobstatus_sk | status_int | status_char | effective_date | expiry_date |
+--------------+------------+-------------+----------------+-------------+
| 1            | 16         | complete    | 1999-12-31     | 9999-12-31  |
| 2            | 32         | failed      | 1999-12-31     | 9999-12-31  |
| 3            | 48         | killed      | 1999-12-31     | 9999-12-31  |
| 4            | 272        | blocked     | 1999-12-31     | 9999-12-31  |
| 5            | 288        | waiting     | 1999-12-31     | 9999-12-31  |
| 6            | 304        | suspended   | 1999-12-31     | 9999-12-31  |
| 7            | 320        | pending     | 1999-12-31     | 9999-12-31  |
| 8            | 323        | waiting     | 1999-12-31     | 9999-12-31  |
| 9            | 325        | badlogin    | 1999-12-31     | 9999-12-31  |
| 10           | 336        | running     | 1999-12-31     | 9999-12-31  |
| 11           | 261        | dying       | 1999-12-31     | 9999-12-31  |
+--------------+------------+-------------+----------------+-------------+

...