Teradata tricks

select * from dbc.SessionInfo;

SELECT   SYSLIB. AbortSessions(1 ,'dbc', 0,'Y' ,'Y') ; -- All sessions for a user

SELECT   SYSLIB. AbortSessions(1 ,'DBC', 123340,'Y' ,'Y') ; -- Specific session

Param1: Hostno

Param2:UserName

Param3: SessionNo

Param4: LogoffSessions

Param5: UserOverride

SELECT * FROM TABLE (MonitorSession (1, '*',0 )) AS dt WHERE PEstate <> 'Idle' or AMPstate <> 'Idle' ;

---

SELECT      TOP 10 username , clientAddr, defaultDatabase , CacheFlag ,

collectTimeStamp ,

StartTime, ElapsedTime , NumResultRows, AMPCPUTime, ERRORCODE ,

StatementType , QueryText , NumOfActiveAmps, SpoolUsage , ReqIoKB ,

ReqPhysIO

FROM dbc. qrylog

WHERE     defaultDatabase LIKE 'TPCDS%'

ORDER    BY StartTime DESC;

BEGIN QUERY LOGGING

WITH      ALL LIMIT SQLTEXT= 0

    ON   ALL;

SELECT TOP 10 *

FROM dbc. QryLogTDWM

ORDER BY collectTimeStamp DESC

SELECT TOP 100 *

FROM dbc. QryLogV

WHERE StatementType IN ('Insert' ,'Update', 'Delete')

ORDER BY collectTimeStamp DESC

SELECT TOP 100 *

FROM dbc. QryLogEvents

ORDER BY collectTimeStamp DESC

---

$ sudo pdestate -a
root's password:
PDE state: DOWN/TDMAINT

$ sudo /etc/init.d/tpa start
Teradata Database Initiator service is starting...
Teradata Database Initiator service started successfully.

$ sudo pdestate -a
PDE state is RUN/STARTED.
DBS state is 1/3: DBS Startup - Starting AMP Partitions

$ sudo pdestate -a
PDE state is RUN/STARTED.
DBS state is 4: Logons are enabled - Users are logged on

---

COLLECT STATISTICS ON tablename COLUMN columnname;

COLLECT STATISTICS ON tablename INDEX ( columnname);

COLLECT STATISTICS ON tablename INDEX ( col1, col2 , ...);

HELP STATISTICS tablename;

COLLECT STATISTICS tablename; -- refresh table statistics

DROP STATISTICS ON tablename ;

DIAGNOSTIC HELPSTATS ON FOR SESSION;

Then run query EXPLAIN. Optimizer will return something like the following at the end of the query plan:

BEGIN RECOMMENDED STATS FOR FINAL PLAN->

     -- "COLLECT STATISTICS COLUMN (I_ITEM_ID) ON TPCDS1000G.item"

     (High Confidence)

     -- "COLLECT STATISTICS COLUMN (D_DATE) ON TPCDS1000G.date_dim"

     (High Confidence)

     -- "COLLECT STATISTICS COLUMN (S_STORE_ID) ON TPCDS1000G.store"

     (High Confidence)

     TPCDS1000G.store_sales" (High Confidence)

     -- "COLLECT STATISTICS COLUMN (P_PROMO_SK) ON TPCDS1000G.promotion"

     (High Confidence)

     -- "COLLECT STATISTICS COLUMN (D_DATE_SK) ON TPCDS1000G.date_dim"

     (High Confidence)

     <- END RECOMMENDED STATS FOR FINAL PLAN

     BEGIN RECOMMENDED STATS FOR OTHER PLANS ->

     -- "COLLECT STATISTICS COLUMN (PARTITION) ON

     TPCDS1000G.store_sales" (High Confidence)

     <- END RECOMMENDED STATS FOR OTHER PLANS

---

Collect Full Statistics

• Non-indexed columns used in predicates
• All NUSIs with an uneven distribution of values * 
• NUSIs used in join steps
• USIs/UPIs if used in non-equality predicates (range constraints)
• Most NUPIs (see below for a fuller discussion of NUPI statistic collection)
• Full statistics always need to be collected on relevant columns and indexes on small tables (less than 100 rows per AMP)

Can Rely on Random AMP Sampling   

• USIs or UPIs if only used with equality predicates
• NUSIs with an even distribution of values
• NUPIs that display even distribution, and if used for joining, conform to assumed uniqueness (see Point #2 under “Other Considerations” below)
• See “Other Considerations” for additional points related to random AMP sampling

Option to use USING SAMPLE

• Unique index columns
• Nearly-unique columns or indexes**

Collect Multicolumn Statistics

• Groups of columns that often appear together in conditions with equality predicates, if the first 16 bytes of the concatenated column values are sufficiently distinct. These statistics will be used for single-table estimates.
• Groups of columns used for joins or aggregations, where there is either a dependency or some degree of correlation among them.***  With no multicolumn statistics collected, the optimizer assumes complete independence among the column values. The more that the combination of actual values are correlated, the greater the value of collecting multicolumn statistics will be.

Basic HBase in Clojure

(println (seq (.getURLs (java.lang.ClassLoader/getSystemClassLoader))) ;; get class path in REPL

# in REPL

(require '[clojure-hbase.core :as hbase])

(import [org.apache.hadoop.hbase HBaseConfiguration HConstants KeyValue])

(import [org.apache.hadoop.hbase.client HTablePool Get Put Delete Scan Result RowLock HTableInterface])

(hbase/set-config (hbase/make-config {

     :zookeeper.znode.parent "/hbase-unsecure" 

     :hbase.zookeeper.property.clientPort "2181"

     :hbase.cluster.distributed "true"

     :hbase.zookeeper.quorum "hdp005-3,hdp005-21,hdp005-23"

}

))

(hbase/table "tweets-test")

Clojure REPL and tricks

lein repl :start :port 10010

(use 'clojure.repl 'clojure.pprint)

(setq nrepl-popup-stacktraces-in-repl nil)
(setq nrepl-auto-select-error-buffer nil)

Zoom

You can use `C-x C-+’ and ‘C-x C--’ (‘text-scale-adjust’) to increase or decrease the buffer text size (`C-+’ or ‘C--’ to repeat).

Very important: add cider plugin in project.clj

(defproject lucy "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :license {:name "Eclipse Public License"
            :url "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [
                 [org.clojure/clojure "1.6.0"]
                 [clucy "0.4.0"]
                 ]
  :plugins [[cider/cider-nrepl "0.6.0"]]
  )

In CIDER

C-c M-n: Set REPL namespace from buffer

C-x C-e: Eval preceding line

C-c C-k: compile buffer

C-x C-c: eval region

C-c C-c: abort eval

M-: Read a single Emacs Lisp expression in the minibuffer, evaluate it, and print the value in the echo area (eval-expression).

C-x h selects the entire buffer.

C-M-\ reindents the selected region.

C-M-@ select s-expression

M-. Jump to the definition of a symbol. If invoked with a prefix argument, or no symbol is found at point, prompt for a symbol.

M-@ (mark-word) puts the mark at the end of the next word

Use M-< to move to the beginning of the buffer, and M-> to move to the end

C-v to scroll down, and M-v to scroll up

M-d Kill up to the end of a word (kill-word).

M-\ Delete spaces and tabs around point (delete-horizontal-space)

M-<SPC> Delete spaces and tabs around point, leaving one space (just-one-space)

M-^ Join two lines by deleting the intervening newline, along with any indentation following it (delete-indentation)

C-t Transpose two characters (transpose-chars).

M-t Transpose two words (transpose-words).

C-M-t Transpose two balanced expressions (transpose-sexps).

C-x C-t Transpose two lines (transpose-lines).

M-x auto-revert-tail-mode (tail a file)

You can hide the *nrepl-connection* and *nrepl-server* buffers from appearing in some buffer switching commands like switch-to-buffer(C-x b) like this:
(setq nrepl-hide-special-buffers t)

---

M-% string <RET> newstring <RET>

Replace some occurrences of string with newstring.

C-M-% regexp <RET> newstring <RET>

Replace some matches for regexp with newstring.

---

<SPC>

to replace the occurrence with newstring.

<DEL>

to skip to the next occurrence without replacing this one.

, (Comma)

to replace this occurrence and display the result. You are then asked for another input character to say what to do next. Since the replacement has already been made, <DEL> and <SPC> are equivalent in this situation; both move to the next occurrence.You can type C-r at this point (see below) to alter the replaced text. You can also type C-x u to undo the replacement; this exits the query-replace, so if you want to do further replacement you must use C-x <ESC> <ESC> <RET> to restart (see Repetition).

<RET>

to exit without doing any more replacements.

. (Period)

to replace this occurrence and then exit without searching for more occurrences.

!

to replace all remaining occurrences without asking again.

Y (Upper-case)

to replace all remaining occurrences in all remaining buffers in multi-buffer replacements (like the Dired `Q' command which performs query replace on selected files). It answers this question and all subsequent questions in the series with "yes", without further user interaction.

N (Upper-case)

to skip to the next buffer in multi-buffer replacements without replacing remaining occurrences in the current buffer. It answers this question "no", gives up on the questions for the current buffer, and continues to the next buffer in the sequence.

^

to go back to the position of the previous occurrence (or what used to be an occurrence), in case you changed it by mistake or want to reexamine it.

---

Print object members

(doseq [m (.getMethods (type index))] (println m))

(use 'clojure.reflect 'clojure.pprint)

(pprint (reflect "hello"))

{:bases
 #{java.io.Serializable java.lang.Comparable java.lang.Object
   java.lang.CharSequence},
 :flags #{:public :final},
 :members
 #{{:name valueOf,
    :return-type java.lang.String,
    :declaring-class java.lang.String,...

List classpath

(defn classpath []

(seq (.getURLs (java.lang.ClassLoader/getSystemClassLoader))))

Expand macros

(macroexpand '(time (print "timing")))

;; or better yet

(clojure.pprint/pprint (macroexpand '(time 1)))

List members in namespace

(require '[clojure-hbase.core :as hbase])

(dir clojure-hbase.core)

Show function source

user=> (source hbase/table)
(defn table
  "Gets an HTable from the open HTablePool by name."
  [table-name]
  (io!
   (.getTable (htable-pool) (to-bytes table-name))))

Find elements by regex match in collection

(filter #(re-find #"zoo" (key %)) (seq (hbase/make-config nil)))

clojure.core/all-ns
  Returns a sequence of all namespaces.

List files

(take 10 (file-seq (clojure.java.io/file ".")))

Basic Zookeeper in Clojure

Add [zookeeper-clj "0.9.1"] to dependencies

lein repl

(require '[zookeeper :as zk])

(.getChildren client "/" false) ;; watcher=false

#<ArrayList [hbase-unsecure, storm, zookeeper]>

(def ZK_HOSTS "127.0.0.1:2181,node1,node2,node3)

(def ZK_ROOT "/twitter-demo")(def client (zk/connect ZOOKEEPER_HOSTS :watcher (fn [event] (println event))))

(zk/create client ZK_ROOT :persistent? false)

(def version (:version (zk/exists client ZK_ROOT)))

(zk/create client ZK_ROOT :data (.getBytes "123143kdjkds") :persistent? false)