I’ve been sending money to support my family member and over years I’ve tried various remittance services. From super bloody fee sucking banking backed service, to super annoying P2P transfer.
It revolves around either reliable but super expensive fees with bad exchange rate, on the extreme end P2P give win-win exchange rate, often people doesn’t mind to deal at spot price but super non reliable (slow, sometime delay responding message – is this guy gonna scam me!?)….
Finally I found transferwise.com which strike the balance between the 2 sides, I found it’s very competitive on the exchange rate, and pretty fast end-to-end transaction (some bank can receive our transfer under 15 mins).
Today mission is to allow user to run super fast query on Azure SQL table. Not any kind of query, it’s a string search query… something like:
SELECT site_id, Site_Name, EnglishEntityName
FROM [dbo].[DIM_SITE]
WHERE (site_id LIKE '%yahoo%'
OR Site_Name LIKE '%yahoo%'
OR Site_lTag LIKE '%yahoo%')
ORDER BY 1 OFFSET 91 ROWS FETCH NEXT 10 ROWS ONLY
The table is not too big, just 80 Millions rows (that’s freaking big actually). Why the query written that way is beyond this post (we need the smart-ass developer to explain that).
Anyway jump to cheat here’s what works for me. It’s running on Azure SQL S6 – 400 DTU
The table need to have 2 indexes: > Clustered on the “ID” > “Non Cluster-including ID” index
Also page compression to make it turbo!
CREATE TABLE [dbo].[RAW_SITE](
[sitekey] [int] NULL,
[sitename] [varchar](250) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[RAW_SITE] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);
CREATE NONCLUSTERED INDEX [idx_site] ON [dbo].[RAW_SITE]
(
[sitename] ASC
)
INCLUDE([sitekey]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [idx_site_key] ON [dbo].[RAW_SITE]
(
[sitekey] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
I don’t usually like to expose my personal financial journey. but I’m trying to cheat here… not in bad way…
So as many of you know, I’m super busy everyday with my IT life. While I have some sort of investment all over the place, I keep hearing about RoboAdvisory from various sources, Web, Chit Chat with friends, etc.
So many different opinion and result, and that’s bad. So with this post I will monitor this syfe portfolio and update at least 1x per week to share the progress.
So the cheating part is for me NOT to manage this robot, in the end what’s the use if we need to still pressing all the decision key is we are already paying Robot to do it.
Cheat Setup Portfolio Reit+ $500 lump sum $100 monthly contribution
Date
Event
Invested
Portfolio Value
Comments
2020.06.20
Create Account
500
500
Setup Account, it took 1-2 working day to get the money processed
2020.06.22
Fund Received
500
0
We have received your funds. It will be invested within 1 – 2 business days.
As usual something disturbing my lovely day. There’s a notification sent to my usually empty mailbox. And it said “Low Disk Space” on one of my Linux server named “LOVELY”.
Then I think “That’s no way it can happen… I shower the server with lots of LOVE”, sigh… Okay time to give it a visit!
After spending time going around and check, finally I found the bad boy. somehow /var/log folder grown too big for unknown reason and I need to know who deploy something and start doing “DEBUG” log on production LOVELY server.
So I press the cheat combo above and I got the result! FOUND YOU!
Another day another story. We are using Azure Bastion to access our Windows VM, it’s a super cool service! Allowing users to RDP to VM without needing to open 3389 on firewall. And as of now like the rest of the world, most people are working from home. I don’t mind to get paid just to keep changing user IP address on firewall every time their home network change IP. But hell, no! That’s the most boring Job. Azure Bastion to the rescue.
A user approached me and ask how can he change password, i said just Press CTRL+ALT+DEL as usually and select Change Password. Then this guy gave me dirty look over his webcam… then only I realized that he is asking for his account on the Azure VM. So I told him the cheat code CTRL+ALT+END! All Happy… all merry!
Cheat Code (this is combo cheat, so pay attention!) > Azure SQL Audit + Blog Storage + XEL File + SSMS!
I’m puzzled, cursing, banging my head, almost throw my laptop (lucky I didn’t do that, so bloody expensive if I did that – My company charge full laptop replacement to our paycheck if we broke it – DARN!)
It started with a crazy developer came to me and said “Bro, I have no idea why my transaction looks weird, the API shall put 26 items, but only 11 item inserted”. (Step 1: fill body with anger) – I said “You DEVELOPER, You WRITE CODE, YOU… YOU… YOU!!!! fine… i’ll check)“
Sometime I have no idea what are they get paid for… Anyway, new things to do on lazy afternoon – not bad.
So I fire up my beloved SSMS, logon to Azure SQL using my super secret account (sysadmin man!) -> Tools -> SQL Server Profiler
Firing up SQL Server Profiler
Then tried to connect to the Azure SQL and I meet my nemesis… “In order to run a trace against SQL server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission” Bloody hell! I am SYSADMIN, I am GOD on this SQL Azure, I can kill it if I want.. Fine! (Step 2: cursing… done)
My nemesis… blocking my doing my GOD job
Googled around and basically in Azure SQL we are not GOD, the Azure team are more GOD-er than us… we are just merely LESSER-GOD than them. Although we pay them… sigh.
So to solve this madness, we need to enable Azure SQL Auditing, pretty simple step. Head over to Azure portal, -> Azure SQL -> Auditing -> ON! I choose Storage, cause it’s easy and accessible (feel free to select other poison)
Enabling Azure SQL Auditing
Once the auditing enabled, I asked the SMART-ASS developer to do whatever he want to do with the DB again.
Then head over to Azure Storage Explorer and check out the baby…
XEL Files hatched!
Download the file and be puzzled… My over-engineering part of my head thought this guy need super damn special tool to open.. so I google and found StackOverflow (here) that provide magical query:
select
n.value('(@name)[1]', 'varchar(50)') as event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('D:\Folder\MySession*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)
Voila! Job done. We found the STUPID mistake the Smart-Developer made.
Then I close all the windows and sipping my coffee until… Accidentally Double clicking the XEL file… And my screen showing MAGICAL GATEWAY to read XEL file… My beloved SSMS just opened and shown the XEL file opened with all the bloody events listed nicely with FILTER, Columns selector…
Darn! There you go… shorter cheat combo actually exists in this world. Have Fun!
import pyodbc
import pandas as pd
pyodbc.autocommit = True
con = pyodbc.connect('DRIVER={Hortonworks Hive ODBC Driver};Host=MySuperCluster.azurehdinsight.net;Port=443;AuthMech=6;SSL=1;UID=SuperAdmin;PWD=[Can'tTellYou];HTTPPath=/hive2;', autocommit=True)
sql = "SELECT * FROM mydb.mytable limit 10"
data = pd.read_sql(sql,con)
print(data)
Another day another Python learning. Today I tried to read the table from Hive which connected using PyODBC into Pandas Data Frame. The idea is, once data available on Pandas Data Frame, I can run my queries and run my exploratory.
So…. recently I’m exploring Python as it’s COOL! What other reason you need to learn new things except that when it’s COOL!
Anyhow, I want to try to compare data between my HDInsight Hive External Table vs CSV using Python. Because I can, so why not 🙂
First challenge in this journey is how the heck can I connect to Hive from Python. Run the cheat code above and you can connect to HDInsight in no time!
Another blunder of Microsoft… My laptop start menu search function was stop working, I can’t search anything using the box. Apparently according to the article it’s due to Bing integration (Thanks Microsoft for giving me something that I don’t care 😕 ). Anyhow using the above 3 combo cheat code fixed it.
Our Azure HDInsight is secured with Enterprise Service Package/ESP (a fancy name saying the cluster is join domain to AD for authentication). Some of the users said they are having difficulties trying to logon from their beloved, most powerful, most flexible BI tool – Excel.
After troubleshooting with our friend and amazing expert from Microsoft Support, we found that the http header size is quite big from our users connection and it hit the default limit.
So, slapping the cheat code above via Ambari then restart the Hive service. And in the end users live happily ever after.
My IT Cheat Code 