Archive for the ‘SQL and NoSQL’ Category

Firebase Quick Tut Transcription

I copied this down from the interactive 5 minute tutorial of Firebase which is useful for storing user-submitted data for mobile apps:

 

Firebase relies on a library that you include in your app. This library gives you easy access to Firebase’s authentication and database features.

To get you started we’ve created an HTML page below. Install the Firebase JavaScript library by adding the following line into the <head> section below:

<script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
1
<html>
2
  <head>
3
    [ADD THE SCRIPT TAG HERE]
4
  </head>
5
  <body>
6
  </body>
7
</html>

2. Accessing your Realtime Database

This tutorial focuses on Firebase’s realtime database. Note that Firebase also has powerful authentication and hosting services.

To access your Firebase database, you’ll first need to create a reference.

References are created using a URL that specifies which data you want to access. We’ve already created a Firebase database specifically for you at this URL: https://u70eg119il2.firebaseio-demo.com/

To complete this step, create a reference to the root of your Firebase database as shown below:

var myDataRef = new Firebase('https://u70eg119il2.firebaseio-demo.com/');
1
<html>
2
  <head>
3
    <script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
4
  </head>
5
  <body>
6
    <script>
7
      [ADD NEW FIREBASE CODE HERE]
8
    </script>
9
  </body>
10
</html>

3. Writing Data

Let’s send a chat message

You can use the reference you just created to write data to your Firebase database using the set() function.

To make things easy, we’ve already added input text boxes for the chatter’s name and message as well as a keypress handler that will fire whenever someone tries to send a message.

For this step, write a message to your Firebase database using the set() function as shown:

myDataRef.set('User ' + name + ' says ' + text);
1
<html>
2
  <head>
3
    <script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
4
    <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js'></script>
5
  </head>
6
  <body>
7
    <input type='text' id='nameInput' placeholder='Name'>
8
    <input type='text' id='messageInput' placeholder='Message'>
9
    <script>
10
      var myDataRef = new Firebase('https://u70eg119il2.firebaseio-demo.com/');
11
      $('#messageInput').keypress(function (e) {
12
        if (e.keyCode == 13) {
13
          var name = $('#nameInput').val();
14
          var text = $('#messageInput').val();
15
          [ADD SET() HERE]
16
          $('#messageInput').val('');
17
        }
18
      });
19
    </script>
20
  </body>
21
</html>

4. Writing Objects

The set() function can also be used to write objects.

Try changing your code to write an object with text and name properties:

myDataRef.set({name: name, text: text});
1
<html>
2
  <head>
3
    <script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
4
    <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js'></script>
5
  </head>
6
  <body>
7
    <input type='text' id='nameInput' placeholder='Name'>
8
    <input type='text' id='messageInput' placeholder='Message'>
9
    <script>
10
      var myDataRef = new Firebase('https://u70eg119il2.firebaseio-demo.com/');
11
      $('#messageInput').keypress(function (e) {
12
        if (e.keyCode == 13) {
13
          var name = $('#nameInput').val();
14
          var text = $('#messageInput').val();
15
          myDataRef.set('User ' + name + ' says ' + text);
16
          $('#messageInput').val('');
17
        }
18
      });
19
    </script>
20
  </body>
21
</html>

5. Writing Lists

The Firebase database supports lists of data.

You’ve already learned how to write data to specific, named locations in the database, but your chat application will require a list of messages. The Firebase database provides a helper function called push() that makes creating lists easy.

Modify your code below to use push() instead of set() so that your chat can support a list of messages (rather than just one):

myDataRef.push({name: name, text: text});
1
<html>
2
  <head>
3
    <script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
4
    <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js'></script>
5
  </head>
6
  <body>
7
    <input type='text' id='nameInput' placeholder='Name'>
8
    <input type='text' id='messageInput' placeholder='Message'>
9
    <script>
10
      var myDataRef = new Firebase('https://u70eg119il2.firebaseio-demo.com/');
11
      $('#messageInput').keypress(function (e) {
12
        if (e.keyCode == 13) {
13
          var name = $('#nameInput').val();
14
          var text = $('#messageInput').val();
15
          myDataRef.set({name: name, text: text});
16
          $('#messageInput').val('');
17
        }
18
      });
19
    </script>
20
  </body>
21
</html>

6. Reading Data

Now let’s receive chat messages.

We need to tell the database to notify us when chat messages arrive. We do this by adding a callback to the list of chat messages using the on() method, as shown below:

myDataRef.on('child_added', function(snapshot) {
  //We'll fill this in later.});

This method takes two arguments: the event type and the callback function. We’ll use the ‘child_added’ event so that we are notified of the arrival of individual messages.

1
<html>
2
  <head>
3
    <script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
4
    <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js'></script>
5
  </head>
6
  <body>
7
    <input type='text' id='nameInput' placeholder='Name'>
8
    <input type='text' id='messageInput' placeholder='Message'>
9
    <script>
10
      var myDataRef = new Firebase('https://u70eg119il2.firebaseio-demo.com/');
11
      $('#messageInput').keypress(function (e) {
12
        if (e.keyCode == 13) {
13
          var name = $('#nameInput').val();
14
          var text = $('#messageInput').val();
15
          myDataRef.push({name: name, text: text});
16
          $('#messageInput').val('');
17
        }
18
      });
19
      [ADD YOUR CALLBACK HERE]
20
    </script>
21
  </body>
22
</html>

7. Using Snapshots

Now we need to display the chat messages on the page.

For each chat message, the database will call your callback with a snapshot containing the message’s data.

Extract the message data from the snapshot by calling the val() function and assign it to a variable. Then, call the displayChatMessage() function to display the message as shown:

var message = snapshot.val();
displayChatMessage(message.name, message.text);
1
<html>
2
  <head>
3
    <script src='https://cdn.firebase.com/js/client/2.2.1/firebase.js'></script>
4
    <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js'></script>
5
  </head>
6
  <body>
7
    <div id='messagesDiv'></div>
8
    <input type='text' id='nameInput' placeholder='Name'>
9
    <input type='text' id='messageInput' placeholder='Message'>
10
    <script>
11
      var myDataRef = new Firebase('https://u70eg119il2.firebaseio-demo.com/');
12
      $('#messageInput').keypress(function (e) {
13
        if (e.keyCode == 13) {
14
          var name = $('#nameInput').val();
15
          var text = $('#messageInput').val();
16
          myDataRef.push({name: name, text: text});
17
          $('#messageInput').val('');
18
        }
19
      });
20
      myDataRef.on('child_added', function(snapshot) {
21
        [MESSAGE CALLBACK CODE GOES HERE]
22
      });
23
      function displayChatMessage(name, text) {
24
        $('<div/>').text(text).prepend($('<em/>').text(name+': ')).appendTo($('#messagesDiv'));
25
        $('#messagesDiv')[0].scrollTop = $('#messagesDiv')[0].scrollHeight;
26
      };
27
    </script>
28
  </body>
29
</html>

T-SQL: Show running queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
— Alternate approach
SELECT s.session_id
,r.
STATUS
,r.blocking_session_id 
‘blocked by’
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 
‘Wait Time (in Sec)’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 
‘Elapsed Time (in Sec)’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1
, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT
)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ +
Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.
host_name
,s.
program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != 
@@SPID
ORDER BY r.cpu_time 
DESC
,r.
STATUS
,r.blocking_session_id
,s.session_id

Performance Benchmark — Storm v Spark v Flink (Re-post)

https://yahooeng.tumblr.com/post/135321837876/benchmarking-streaming-computation-engines-at

T-SQL: Select / list all tables of a database

USE YourDBName
GO
SELECT *
FROM sys.Tables
Go
 microsoft-sql-00

SQL Server: Delete all tables in a database (T-SQL)

USE MyDB
GO
EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”
GO
microsoft-sql-00

Hive: Make CLI output files comma delimited

bash >> hive -e ‘select * from some_Table’ | sed ‘s/[\t]/,/g’ > outputfile.txt

Here [\t] means Control+V and then the tab button, i.e.
sed ‘s//,/g’

Example:

[user@server]$ hive -e "use dbname ; select * from tablename" | sed ‘s/ /,/g’ > kpi_event_jan8.csv

Hive: Get Column Names in CLI Queries

Add this to your query:

set hive.cli.print.header=true;

Example:

hive -e "set hive.cli.print.header=true; use a_db; select * from a_table;" > test

hive_logo_medium

Quick Tip: How to Fix “Invalid Object Name” for New Tables in SQL Server Management Studio (SSMS)

This big headache has a simple solution:

Edit -> IntelliSense -> Refresh Local Cache

SQL Server / T-SQL: ROLLUP(), CUBE(), GROUPING_SETS()

Note: Much of this was just pulled from the language manual; still, I find it a useful extract with and I’ve added a little commentary.

ROLLUP ( )Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.
The number of groupings that is returned equals the number of expressions in the plus one. For example, consider the following statement.

SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c);


One row with a subtotal is generated for each unique combination of values of (a, b, c), (a, b), and (a). A grand total row is also calculated.
Columns are rolled up from right to left. The column order affects the output groupings of ROLLUP and can affect the number of rows in the result set.
CUBE ( )Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.
CUBE outputs a grouping for all permutations of expressions in the <composite element list>.
The number of groupings that is generated equals (2n), where n = the number of expressions in the <composite element list>. For example, consider the following statement.

SELECT a, b, c, SUM (<expression>)
FROM T
GROUP BY CUBE (a,b,c);

One row is produced for each unique combination of values of (a, b, c), (a, b), (a, c), (b, c), (a), (b) and (c) with a subtotal for each row and a grand total row.
Column order does not affect the output of CUBE.
GROUPING SETS ( )Specifies multiple groupings of data in one query. Only the specified groups are aggregated instead of the full set of aggregations that are generated by CUBE or ROLLUP. The results are the equivalent of UNION ALL of the specified groups. GROUPING SETS can contain a single element or a list of elements. GROUPING SETS can specify groupings equivalent to those returned by ROLLUP or CUBE. The can contain ROLLUP or CUBE.
( )The empty group generates a total.

SQL: Compare Columns of 2 Tables

SELECT * FROM (
SELECT * FROM Jason.INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME='cool_table'
) a FULL OUTER JOIN (
SELECT * FROM [SOMESERVER].[SOMEDB] .INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME='DIM_Product'
) b ON a.COLUMN_NAME =b. COLUMN_NAME
WHERE a .COLUMN_NAME IS NULL OR b.COLUMN_NAME IS NULL
OR a .DATA_TYPE<> b.DATA_TYPE OR a.IS_NULLABLE <>b. IS_NULLABLE
OR a .CHARACTER_MAXIMUM_LENGTH<> b.CHARACTER_MAXIMUM_LENGTH
OR a .NUMERIC_PRECISION<> b.NUMERIC_PRECISION OR a.NUMERIC_SCALE <>b. NUMERIC_SCALE
OR a .COLLATION_NAME<> b.COLLATION_NAME -- and maybe some other columns