Author: Cindy Gross | Befriending Dragons

  • Serenity for Surgery

    Surgery. The word seems to terrify a lot of people but I’m fascinated, kind of like River Tam reading Shepherd Book’s book. I tried to get them to agree to take pictures during the procedure but haven’t had any luck with that so far. I asked if I could stay awake and watch but that is apparently not an option either. They tell me there are a mix of privacy, lawsuit, and infection concerns plus they don’t want to distract the surgeon. Sigh. I bet Dr. Simon Tam would take pics during surgery if I asked… at least if I looked like Kaylee or Inara.

    Serenity

    Friday I am having a lumpectomy, also known as breast conservation surgery. From a clinical perspective they are simply removing what is estimated to be a 1.3cm tumor and 1-2 lymph nodes through a small incision (maybe two incisions) that is expected to heal with little scarring. I expect Dr. Szentes to have the mad piloting skills of Wash and the brash, confident command of Captain Mal as he operates. Or even better, the mechanical skills of Kaylee. It’s outpatient surgery and I will go home within a few hours of the end of surgery. In about 3-5 days I get pathology results back to confirm that they got the entire tumor and that it doesn’t appear to have spread like Jayne on a drunken spree with Vera. Given the early detection, low grade, and small size of the tumor they are pretty confident it hasn’t spread. The bigger concern is whether I get another in the future. My mom had breast cancer twice and the second one killed her. However, treatment in the 1990s when she got sick and treatment and screening now are very different. they will prescribe Tamoxifen and I may take other steps to reduce my estrogen exposure. No matter how my BRCA results turn out (genetic testing is due back around September 13) I will be on an every 6 months screening schedule, alternating MRIs and mammograms, for the foreseeable future. With that level of “enhanced screening” my survival rate from breast cancer is actually higher than the general population, certainly higher than if I ran into a bunch of Reavers. So ladies (and high risk men!) don’t put off your mammograms and get to know your own boobs!

    Engineered by Firefly

    Everyone else seems more concerned about this surgery than I am. I look at it as an inconvenience that will make me uncomfortable for a few days. I’m grateful they caught the cancer early, it’s non-aggressive, and I have a clear, relatively easy treatment plan. The advances they’ve made in surgery (they take only a small margin around the tumor in most non-aggressive cases) and radiation (very targeted and much less troublesome than chemo) are remarkable. I’ve asked a ton of questions and explored all sorts of options such as new types of genetic testing on tumors (EXON 7 testing for example is too new and unproven and other genetic markers in the tumor would prove the same thing) and bio-preserving my tumor for research (Boise has a bio research facility and it looks likely I will be able to participate). I am very lucky to have a nurse navigator assigned by St. Luke’s to help answer all my questions, track down providers, and help me through any sort of concerns I have. She’s very no-nonsense like Zoe and helpful like Shepherd Book.

    In the meantime I am enjoying my birthday (it’s today but I had a fun costume party at my house Sunday night with many great friends) and my vacation (tubing the Boise River, multiple movies, friends visiting from Phoenix, walking the greenbelt, great food and wine). I wish I could visit Jaynestown too but that will have to wait a few hundred years. I’ll take a couple of days off after the surgery; more to make sure I’m relaxed and caught up on personal things than because I expect to truly need it for recovery. It will be about a month or so before I start radiation, then we’ll be on to the enhanced screening and I’ll continue to get on with my life. This surgery and the cancer is just a small bump in the road and I’m looking forward to moving on past it so I can live long and prosper. The future is looking Shiny because no power in the ‘verse can stop me! And I definitely aim to misbehave because you can’t take the sky from me!

  • A Boobtastic MRI Experience

    Today I had my first ever MRI – in this case a boobtastic MRI. They’re using the MRI to look for blood supplies in both of my breasts. Cancers need a lot of blood to grow and thrive so that’s why MRIs are used in cancer screening – they look for unexpected blood supplies. However, many other things can also cause out of the ordinary blood supplies and many of these are in no way dangerous. This means there’s a high chance of false positives that lead to additional screening such as ultrasounds that focus in on the area with the extra blood supplies. Today’s MRI will help them better estimate the size of the tumor in my left breast to increase the chance they take the optimal amount of tissue during the lumpectomy. They aim to get “clear margins” meaning they remove a bit of non-tumorous tissue around the tumor without missing any of the tumor or taking more tissue than needed. The MRI could also identify other potential “excessive” blood supplies that might need further investigation before surgery. I’m looking at a future screening schedule that includes an MRI every 12 months for the rest of my life since I’m a high risk patient. That will alternate with mammograms so that I have some sort of screening every 6 months. This “enhanced screening” greatly increases survival rates in high risk patients to the point that high risk patients are actually more likely to survive a cancer than someone in the general population who may or may not get screened regularly. So gals – get screened as often as your doctor recommends and get familiar with your own boobs!

    Originally my MRI was scheduled for 430p at the downtown Boise St. Luke’s very near my house. However, I got a call around 1130a saying that there were some emergencies that needed the MRI and they could not get me into the Boise location today. Would I be able to arrive at their Meridian hospital by 1230 because they had an opening there? I quickly checked with my chauffeur, Linda Crozier, and verified that yes, this would work. She ditched her husband and their planned movie and picked me up immediately. We were “all the way in Meridian” (those of you in Boise will understand that to mean “almost in a foreign country”) by 1215p. I popped my prescribed anti-anxiety pill as we pulled into the parking lot. If they offer you an anti-anxiety pill when they’re scheduling the appointment then take the pill – why not? They quickly checked us in and I was back in the MRI waiting room within a few minutes. Linda opted to wait in the main waiting room as they wouldn’t let her observe the actual MRI any way.

    They let me keep on my metal-free yoga pants and thick, warm socks but had me put on scrubs for a top. They verified yet again that I was wearing no metal, had never had an accident involving metal in my eyes (!), and that the only metal embedded in me was the plate in my left arm (per the tech that isn’t ferrous and can’t cause harm in an MRI) and the clip they inserted in my breast to mark the tumor.

    Boob MRI for breast cancer screening
    Boob MRI for breast cancer screening

    Within a few more minutes I was in the MRI room. That’s when I first saw the pink contraption pictured here. I guess they figure anything breast related must be pink because all women just love pastels and pink in particular… right…. NOT! They had me sit on the side of the table while they inserted a catheter in my arm so they could add some dye later in the process. Then they had me get as comfortable as possible on the boob contraption. The boobs themselves just hung in two holes (labeled conveniently as left boob and right boob in the picture). My head fit in a massage-table-like head rest. My arms were stretched out in front of me. Then they figured out the right combination of blankets and pillows under my rib cage, legs, and feet to make me comfortable enough that I wasn’t likely to squirm and added a blanket on top to keep me warm. If you move during the 20-45 minute MRI they have to redo pictures and stay even longer so comfort is key. They hooked up a line to the catheter in my arm, put some big headphones on me (with music piped in from my iPod), put a call button in my hand (they can’t hear me call out over the loud noises the machine makes), and pushed me back into the MRI machine.

    The machine had been making clunking noises of various sorts the whole time, but now the noises became more consistent and louder, though the specific sounds changed quite a bit. The machine is taking many, many pictures, each one of a different 3mm slice of my breasts. I just concentrated on breathing deeply and listening to my music. They had placed a double mirror under the headrest so I could see out the end of the tunnel. I watched that for a while but it was not very interesting – a very narrow view of the room between my fingers. So I closed my eyes and relaxed. After about 10 minutes I heard a voice over the headphones saying they were injecting the dye and I could feel a cold spot in my arm for a few seconds. I have a vague memory of hearing an extra loud noise and jerking at some point. I think the memory was vague because I was asleep…. The tech’s voice came over the headphones saying I had moved so they would have to redo a couple of the pics. I was very relaxed at that point and may have gone back to sleep – it seemed to be over very quickly.

    They came in and pushed me out of the tube. They had me sit up and they removed the catheter then said I could leave. I asked if I could grab my phone from the waiting room and take a picture. They said sure. I couldn’t take the phone inside the room but was able to take the pic from the doorway. I said I needed it for my blog and the tech said, yes, people do that all the time. Who knew so many of us thought other people would care about our MRI experiences!

    After changing back into my street clothes I collected Linda and we headed back to Boise for a coffee fix at Starbucks. They already have Pumpkin Spice available! We relaxed in the Starbucks for a while then Linda dropped me off and headed out to get back to her day.

    It generally takes 24-48 hours for a mammo-radiologist to review and report on the results. I really hope they get to it before the end of the day Friday because they aren’t working on Monday due to the Labor Day holiday. I would really like to have confirmation that the MRI has no surprises so I can plan for a Sept 6 lumpectomy. If the Sept 6 surgery date doesn’t work out we may be back to the original Sept 25 surgery I was hoping for when I was thinking I wanted to wait for my BRCA results (I’ll cover that in a separate blog).

    I hope you found my boob’s boobtastic journey through the MRI entertaining and/or educational. 🙂 For anyone trying to calm your nerves before having your own MRI – it was easy! Just make sure you are comfortable, ask tons of questions ahead of time and as they are prepping you for the MRI, and relax.

  • Climbing the Tree of Life

    Tree of LifeThis week I got news that most will take as bad. But there are some very good things about it. I may have cancer, but it’s a very non-aggressive, easily treatable type. We caught it early and have a solid treatment plan that should completely remove it. There are a few test results we’re waiting on but all-in-all my prognosis is good. What do I do with this new found kick in the pants? Cancer is supposed to be a life changing event that makes you re-evaluate everything and choose new directions. What direction do I want to take? How radical of a change? In what part(s) of my life? Is all this re-evaluating even the right thing to do? It does make me question a lot of things but honestly the biggest thing I want to rush out and do is some of the traveling I’ve been putting off. I want to go to Greece – visit the ancient ruins, sit on the beach, and just relax. I want to start crossing off the remaining US states that I haven’t yet been to – less than half are left but a few I’ve only been to for work or as a kid and I would like to have a “fun” memory of each. I would like to visit most of the continents: Antarctica might just be too cold but the rest all have many interesting possibilities. I want to keep having fun, always learn more, and contribute what I can to society. I am realizing that the details may not matter so much – I just need to keep climbing the tree of life.

  • Don’t Be Sorry

    Don’t be sorry.TinyDragonOnFingers

    There are so many ways to look at that. Don’t be sorry you did something yourself. Don’t feel sorry for someone else. Don’t dwell on the negative…. Right now I just want you to avoid telling me you’re sorry. I want to hear “hey – it’s going to be alright” or “you’re lucky”. Why, you ask?

    Well, I was diagnosed on August 19 with breast cancer. We caught it early, it’s non-aggressive, it’s small, and it’s considered very treatable. Don’t feel sorry for me and don’t say you’re sorry. If I’m going to have cancer this is about as good as it gets. Think positive, help me plan for the future, and treat me like normal. And of course indulge my every whim and let me win every argument…. Just kidding. Mostly. Many of you want to help and I do appreciate that. Let me put your offers on hold as I am told help is most needed a few months into the treatment plan.

    Thank you for your caring and for your support!

    I’ll update my medical details regularly here: http://www.caringbridge.org/visit/cindygross

    Follow me at https://twitter.com/cindygross.

  • Big Data Twitter Demo

    Real-time. Social Sentiment Analysis. Twitter. Cloud. Insights. We have your Big Data buzzwords here!

    Everyone seems to want to incorporate social sentiment into their business analysis. Well we have the demo for you! Use it for a quick demonstration of what can be done and when the excitement goes through the roof, use it to inspire your own design!

    Real-Time Processing – Instant Insights!

    First, use Event Driven Processing (EDP) to show the data on a dashboard. In the demo you’ll use StreamInsight, also referred to as Complex Event Processing (CEP), though you may want to use Microsoft Open Technologies’s RX / Reactive Extensions in your own project. Use the dashboard to make real-time decisions and take immediate action. For example, configure your EDP to “pop” only on terms related to your company and your marketing analyst can watch how the volume of tweets and the sentiment (measured positive/neutral/negative in this example) change in response to your Super Bowl ad or a mention of your company on a the news show. She can respond instantly with changes to your website, your own tweets, sales/promotions, or whatever is appropriate to your business.

    Data Storage – Enable Insights!

    EDP reads the data as it is pushed through a query, there is no inherent storage involved. You could just discard the data and never store it. In this example we chose to store the data for later trending and historical analysis. We take the tweet id, the date/time the tweet was captured, the sentiment score calculated during the real-time processing, and the keyword that caught our attention and store it in SQL Azure. This data is available to other applications that need to join it with existing data and requires fast responses to individual queries. The remaining data including the raw tweet, any geographic data the tweeter chose to share, and other data is dropped in an Azure Blob Store.

    Trends, Patterns, and Historical Insights!

    Now point HDInsight (Hadoop) to the Azure Blob Storage using HDInsight’s ASV extension to HDFS. You can spin up a Hadoop cluster in Azure, pay for as many nodes as you need for as long as you need them then spin them down to save money. The data remains in the blob store – available for future Hadoop clusters, other applications, archival, or whatever you need it for. Add structure to the JSON data with Hive and now you have rows and columns that can be accessed by BI tools!

    Visualization for Powerful Insights!

    Now create a PowerPivot tabular model (self-service BI / client side) or an Analysis Service tabular model (corporate BI) to store the relevant data in a highly compressed, in-memory format for fast access. Add in a few Power View visualizations mashing up data from multiple structured and unstructured sources and you can show your business decision makers easily digestible and understandable data in a format they just get and love! Make some decisions, take some actions, and you’ve just shown how to turn free Twitter data into a valuable resource that can have a direct impact on your company!

    How to Get These Insights

    Follow the instructions on the CodePlex site for the Big Data Twitter Demo project. Set it up, run through the demo, get excited, and go improve your business!

    Demo Created By:

    Vu Le

    Andrew Moll

    Aviad Ezra @aviade_pro

    Brad Sarsfield @Bradoop

    Later Additions By:

    Lara Rubbelke @SQLGal | http://sqlblog.com/blogs/lara_rubbelke/

    Robert Bruckner http://blogs.msdn.com/b/robertbruckner/

    Cindy Gross @SQLCindy | http://blogs.msdn.com/cindygross

  • Access Azure Blob Stores from HDInsight

    Small Bites of Big Data

    Edit Mar 6, 2014: This is no longer necessary for HDInsight – you specify the storage accounts when you create the cluster and the rest happens auto-magically. See http://blogs.msdn.com/b/cindygross/archive/2013/11/25/your-first-hdinsight-cluster-step-by-step.aspx or http://blogs.msdn.com/b/cindygross/archive/2013/12/06/sample-powershell-script-hdinsight-custom-create.aspx.

    One of the great enhancements in Microsoft’s HDInsight distribution of Hadoop is the ability to store and access Hadoop data on an Azure Blob Store. We do this via the HDFS API extension called Azure Storage Vault (ASV). This allows you to persist data even after you spin down an HDInsight cluster and to make that data available across multiple programs or clusters from persistent storage. Blob stores can be replicated for redundancy and are highly available. When you need to access the data from Hadoop you point your cluster at the existing data and the data persists even after the cluster is spun down.

    Azure Blob Storage

    Let’s start with how your data is stored. A storage account is created in the Azure portal and has access keys associated with it. All access to your Azure blob data is done via storage accounts. Within a storage account you need to create at least one container, though you can have many. Files (blobs) are put in the container(s). For more information on how to create and use storage accounts and containers see: http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/. Any storage accounts associated with HDInsight should be in the same data center as the cluster and must not be in an affinity group.

    You can create a container from the Azure portal or from any of the many Azure storage utilities available such as CloudXplorer. In the Azure portal you click on the Storage Account then go to the CONTAINERS tab. Next click on ADD CONTAINER at the very bottom of the screen. Enter a name for your container, choose the ACCESS property, and click on the checkmark.

    AzureStorageContainers_thumb AzureStorageManageContainers_thumb AzureStorageNewContainer_thumb

    HDInsight Service Preview

    When you create your HDInsight Service cluster on Azure you associate your cluster with an existing Azure storage account in the same data center. In the current interface the QUICK CREATE doesn’t allow you to choose a default container on that storage account so it creates a container with the same name as the cluster. If you choose CUSTOM CREATE you have the option to choose the default container from existing containers associated with the storage account you choose. This is all done in the Azure management portal: https://manage.windowsazure.com/.

    Quick Create: image

    Custom Create: image

    You can then add additional storage accounts to the cluster by updating C:appsdisthadoop-1.1.0-SNAPSHOTconfcore-site.xml on the head node. This is only necessary if those additional accounts have private containers (this is a property set in the Azure portal for each container within a storage account). Public containers and public blobs can be accessed without the id/key being stored in the configuration file. You choose the public/private setting when you create the container and can later edit it in the “Edit container metadata” dialog on the Azure portal.

    image StorageContainerEdit

    The key storage properties in the default core-site.xml on HDInsight Service Preview are:

    <property>
    <name>fs.default.name</name>
    <!– cluster variant –>
    <value>asv://YOURDefaultContainer@YOURStorageAccount.blob.core.windows.net</value>
    <description>The name of the default file system.  Either the
    literal string “local” or a host:port for NDFS.</description>
    <final>true</final>
    </property>

    <property>
    <name>dfs.namenode.rpc-address</name>
    <value>hdfs://namenodehost:9000</value>
    </property>

    <property>
    <name>fs.azure.account.key.YOURStorageAccount.blob.core.windows.net</name>
    <value>YOURActualStorageKeyValue</value>
    </property>

    To add another storage account you will need the Windows Azure storage account information from https://manage.windowsazure.com. Log in to your Azure subscription and pick storage from the left menu. Click on the account you want to use then at the very bottom click on the “MANAGE KEYS” button. Cut and paste the PRIMARY ACCESS KEY (you can use the secondary if you prefer) into the new property values we’ll discuss below.

    AzureStorageAccounts_thumb AzureStorageContainerKeys_thumb1 AzureStorageMgKeys_thumb1

    Create a Remote Desktop (RDP) connection to the head node of your HDInsight Service cluster. You can do this by clicking on the CONNECT button at the bottom of the screen when your HDInsight Preview cluster is highlighted. You can choose to save the .RDP file and edit it before you connect (right click on the .RDP file in Explorer and choose Edit). You may want to enable access to your local drives from the head node via the “Local Resources” tab under the “More” button in the “Local devices and resources” section. Then go back to the General tab and save the settings. Connect to the head node (either choose Open after you click CONNECT or use the saved RDP).

    image image

    On the head node make a copy of C:appsdisthadoop-1.1.0-SNAPSHOTconfcore-site.xml in case you have to revert back to the original. Next open core-site.xml in Notepad or your favorite editor.

    Add your 2nd Azure storage account by adding another property.

    <property>
    <name>fs.azure.account.key.YOUR_SECOND_StorageAccount.blob.core.windows.net</name>
    <value>YOUR_SECOND_ActualStorageKeyValue</value>
    </property>

    Save core-site.xml.

    Repeat for each storage account you need to access from this cluster.

    HDInsight Server Preview

    If you have downloaded the on-premises HDInsight Server preview from http://microsoft.com/bigdata that gives you a single node “OneBox” install to test basic functionality. You can put it on your local machine, on a Hyper-V virtual machine, or in a Windows Azure IaaS virtual machine. You can also point this OneBox install to ASV. Using an IaaS VM in the same data center as your storage account will give you better performance, though the OneBox preview is meant purely for basic functional testing and not for high performance as it is limited to a single node. The steps are slightly different for on-premises as the installation directory and default properties in core-site.xml are different.

    Make a backup copy of C:Hadoophadoop-1.1.0-SNAPSHOTconfcore-site.xml from your local installation (local could be on a VM).

    Edit core-site.xml:

    1) Change the default file system from local HDFS to remote ASV

    <property>
    <name>fs.default.name</name>
    <!– cluster variant –>
    <value>hdfs://localhost:8020</value>
    <description>The name of the default file system.  Either the
    literal string “local” or a host:port for NDFS.</description>
    <final>true</final>
    </property>

    to:

    <property>
    <name>fs.default.name</name>
    <!– cluster variant –>
    <value>asv://YOURDefaultContainer@YOURStorageAccount.blob.core.windows.net</value>
    <description>The name of the default file system.  Either the
    literal string “local” or a host:port for NDFS.</description>
    <final>true</final>
    </property>

    2) Add the namenode property (do not change any values)

    <property>
    <name>dfs.namenode.rpc-address</name>
    <value>hdfs://namenodehost:9000</value>
    </property>

    3) Add the information that associates the key value with your default storage account

    <property>
    <name>fs.azure.account.key.YOURStorageAccount.blob.core.windows.net</name>
    <value>YOURActualStorageKeyValue</value>
    </property>

    4) Add any additional storage accounts you plan to access

    <property>
    <name>fs.azure.account.key.YOUR_SECOND_StorageAccount.blob.core.windows.net</name>
    <value>YOUR_SECOND_ActualStorageKeyValue</value>
    </property>

    Save core-site.xml.

    Files

    Upload one or more files to your container(s). You can use many methods for loading the data including Hadoop file system commands such as copyFromLocal or put, 3rd party tools like CloudXPlorer, JavaScript, or whatever method you find fits your needs. For example, I can upload all files in a data directory (for simplicity this sample refers to C: which is local to the head node) using the Hadoop put command:

    hadoop fs -put c:data asv://data@sqlcatwomanblog.blob.core.windows.net/

    Or upload a single file:

    hadoop fs -put c:databacon.txt asv://data@sqlcatwomanblog.blob.core.windows.net/bacon.txt

    To view the files in a linked non-default container or a public container use this syntax from a Hadoop Command Line prompt (fs=file system, ls=list):

    hadoop fs -ls asv://data@sqlcatwomanblog.blob.core.windows.net/

    Found 1 items
    -rwxrwxrwx   1        124 2013-04-24 20:12 /bacon.txt

    In this case the container data on the private storage account sqlcatwomanblog has one file called bacon.txt.

    For the default container the syntax does not require the container and account information. Since the default storage is ASV rather than HDFS (even for HDInsight Server in this case because we changed it in core-site.xml) you can even leave out the ASV reference.

    hadoop fs -ls asv:///bacon.txt
    hadoop fs -ls /bacon.txt

    More Information

    I hope you’ve enjoyed this small bite of big data! Look for more blog posts soon.

    Note: the Preview, CTP, and TAP programs are available for a limited time. Details of the usage and the availability of the pre-release versions may change rapidly.

    Digg This
  • Self-Service BI Works!

    When I talk to people about adding self-service BI to their company’s environment I generally get a list of reasons why it won’t work. Some things I commonly hear:

    • I can’t get anyone in IT or on the business side to even try it.
    • The business side doesn’t know how to use the technology.
    • This threatens my job.
    • I just don’t know where to start either politically/culturally or with the technology.
    • I have too many other things to do.
    • How can it possibly be secure, allow standardization, or result in quality data and decisions?
    • That’s not the way we do things.
    • I don’t really know what self-service BI means.

    #PASSBAC 2013 Cindy and Eduardo 

    So what is a forward thinking BI implementer to do? Well, Intel just went out and did it, blowing through the supposed obstacles. Eduardo Gamez of Intel’s Technology Manufacturing Engineering (TME) group interviewed business folks to find those who were motivated for change, found a great pilot project with committed employees, and drove the process forward. They put a “sandbox” environment up for the business to use and came up with a plan for monitoring the sandbox activity to find models and reports worth adding to their priority queue for enterprise BI projects. The business creates their own data models and their own reports for both high and low priority items. IT provides the infrastructure and training including products like Analysis Services, PowerPivot, Power View, SharePoint, Excel, SQL Server, and various data sources. The self-service models and reports are useful to the business – they reduce manual efforts, give them the reports they want much faster, and ultimately drive better, more agile business decisions. If a model isn’t quite right after the first try, they can quickly modify it. The same models and reports are useful to IT – they are very refined and complete requirements docs that shorten the time to higher quality enterprise models and reports, they free up IT resources to build a more robust infrastructure and allow IT to concentrate on projects that require specialized IT knowledge. Everyone wins with a shorter time to decision, higher quality decisions, and a significant impact on the bottom line.

    Learn more about how Intel TME is implementing self-service BI:

    Eduardo (eduardo.m.gamez@intel.com) and I (cgross@microsoft.com or @SQLCindy) are happy to talk to you about Self-Service BI – let us know what you need to know!

    Digg This

    How_Intel__Integrates_Self-Service_BI_with_IT_for_Better_Business_Results_[DAV-208-M].zip

  • HDInsight: Jiving about Hadoop and Hive with CAT

    Tomorrow I will be talking about Hive as part of Pragmatic Work’s Women in Technology (WIT) month of webcasts. I am proud to be part of this lineup with all these stellar WITs! I encourage my fellow WITs to get more involved in your data community and if you don’t already do so start tweeting, blogging, and speaking. I am happy to coach you through your first speaking engagement if you are interested. Get out there and start showing the world what you can do!

    Thursday’s talk is going to be HDInsight: Jiving about Hadoop and Hive with CAT. Let’s break that title down.

    HDInsight is Microsoft’s distribution of Hadoop. As part of the HDInsight project we have checked code back into the core Apache Hadoop source code to make the core code runs great on Windows. We are also adding functionality and features such as JavaScript and Azure Storage Vault that make the product more robust and enterprise friendly. This week the HDInsight Service Preview on Azure became available to those with an Azure subscription.

    Hadoop is a scale out methodology that allows businesses to quickly consume and analyze data in ways they haven’t been able to before. This can lead to faster, better business insights and business actions.

    Hive is a way to impose metadata and structure on the loosely structured (unstructured, multi-structured, semi-structured) data that resides in Hadoop’s HDFS file system. With Hive and the Hive ODBC driver you can make Hadoop data look like any other data source to your familiar BI tools such as Excel. PowerPivot can connect to Hive data, mash that data up with existing data sources such as SQL Azure, SQL Server, and OData, and allow you to visualize it with Power View. I have an end to end demo of this: Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View.

    CAT is my team at Microsoft. The Customer Advisory Team (CAT) works with customers who are doing new, unusual, and interesting things that push the boundaries of technology. We share what we learn with the community so you can do your jobs better and we take what we learn from you to the product team to help improve the product.

    My slides are attached at the bottom of this post. I believe a recording of the talk will be posted by Pragmatic Works on their site.

    I look forward to “seeing” you all at my talk tomorrow and would love to see your tweets or hear directly from you afterwards.

    I hope you’ve enjoyed this small bite of big data! Look for more blog posts soon on the samples and other activities.

    Note: the CTP and TAP programs are available for a limited time. Details of the usage and the availability of the CTP may change rapidly.

    Digg This

    PragmaticWorksHDInsightJivingAboutHadoopAndHiveWithCATMar202013.pptx

  • PASS BAC PREVIEW SERIES: SQL Professionals and the World of Self-service BI and Big Data

    Are you excited about the upcoming PASS Business Analytics Conference? You should be! This conference will offer a wide range of sessions about Microsoft’s End to End Business Intelligence (including Self-Service BI), Analytics, Big Data, Architecture, Reporting, Information Delivery, Data Management, and Visualization solutions. Whether you are an implementer, a planner, or a decision maker there is something here for you!

    PASS_BAC_Horizontal_Banner

    What makes this conference different? Why should you put in the effort to attend this conference in particular? We are seeing a paradigm shift focused on shorter time to decision, more data available than ever before, and the need for self-service BI. There are exciting technology solutions being presented to deal with these needs and new architectural skills are needed to implement them properly. Self-Service BI and Big Data are very different in many ways but also responding to the same problem – the need for additional insights and less time spent getting to those insights and the resulting impactful decisions. Self-Service BI via PowerPivot, Power View, Excel, and existing and new data sources including HDInsight/Hadoop (usually via Hive) offers fast time to decision, but you still sometimes need Enterprise BI to add additional value via services such as data curation, data stewardship, collaboration tools, additional security, training, and automation. Add in the powerful new data sources available with Big Data technologies such as HDInsight/Hadoop that can also reduce time to decision and open up all sorts of new opportunities for insight and you have many powerful new areas to explore. Not to mention that Dr. Steven Levitt, author of Freakonomics and SuperFreakonomics, is one of the keynote speakers!

    Read more about my thoughts on Self-Service BI and Big Data in this #PASSBAC guest blog published today: PASS BAC PREVIEW SERIES: SQL Professionals and the World of Self-service BI and Big Data

    And sign up for the session I am co-presenting at #PASSBAC with Eduardo Gamez of Intel: How Intel Integrates Self-Service BI with IT for Better Business Results

    Take a look at all the information tagged with #PASSBAC and tweeted by @PASSBAC, there are some good blogs, preview sessions, and tidbits being posted. Get your own Twibbon for Twitter, Facebook, or however you want to use it, the Twibbon site will add a ribbon to the picture of your choice:

    PASSBA2013Cindy

    If you’re going to be in Chicago anyway, you might as well stay a few extra days for two nearby SQL Saturdays. The weekend before the conference take a short hop over to Madison, WI for #SQLSAT206 on April 6, 2013 at the Madison Area Tech College. Then head over to the bacon, uhhh, PASS BA CONference April 10-12. Stay one more day in Chicago (technically Addison, IL) for the #SQLSAT211 sessions at Devry. This is a great opportunity for even more SQL Server immersion and networking!

    See you at #PASSBAC in Chicago in April!

    @SQLCindy

    Small Bites of Big Data

  • HDInsight: Hive Internal and External Tables Intro

    Small Bites of Big Data

    Cindy Gross, SQLCAT PM

    HDInsight is Microsoft’s distribution, in partnership with Hortonworks, of Hadoop. Hive is the component of the Hadoop ecosystem that imposes structure on Hadoop data in a way that makes it usable from BI tools that expect rows and columns with defined data types. Hive tables can be created as EXTERNAL or INTERNAL. This is a choice that affects how data is loaded, controlled, and managed.

    Use EXTERNAL tables when:

    • The data is also used outside of Hive. For example, the data files are read and processed by an existing program that doesn’t lock the files.
    • Data needs to remain in the underlying location even after a DROP TABLE. This can apply if you are pointing multiple schemas (tables or views) at a single data set or if you are iterating through various possible schemas.
    • You want to use a custom location such as ASV.
    • Hive should not own data and control settings, dirs, etc., you have another program or process that will do those things.
    • You are not creating table based on existing table (AS SELECT).

    Use INTERNAL tables when:

    • The data is temporary.
    • You want Hive to completely manage the lifecycle of the table and data.

    We’ll walk through creating basic tables with a few rows of data so you can see some of the differences between EXTERNAL and INTERNAL tables. The demo data files are attached at the bottom of the blog. Alternatively you can simply open notepad and create your own files with a series of single column rows. If you create your own files make sure you have a carriage return/line feed at the end of all rows including the last one. The files should be in a Windows directory called c:data on the HDInsight Head Node. For HDInsight Server (on-premises) that’s the machine where you ran setup. For HDInsight Services (Azure) you can create a Remote Desktop connection (RDP) to the head node from the Hadoop portal.

    Note: Your client tool editor or the website may change the dashes or other characters in the following commands to “smart” characters. If you get syntax errors from a direct cut/paste, try pasting into notepad first or deleting then retyping the dash (or other special characters).

    Create an HDInsight cluster. You can do this on your own Windows machine by installing HDInsight Server or by signing up for HDInsight Services on Azure. For the CTP of HDInsight Services as of February 2013 you fill out a form to request access and receive access within a few days. Soon the service will be available from the Azure portal via your Azure subscription. Since the portal interface will be changing soon and all the commands are straightforward I will show you how to do all the steps through the Hive CLI (command line interface).

    Open a Hadoop Command Prompt:

    HadoopCmdPrompt

    Change to the Hive directory (necessary in early preview builds of Hive):

    cd %hive_home%bin

    Load some data (hadoop file system put) and then verify it loaded (hadoop file system list recursively):

    hadoop fs -put c:databacon.txt /user/demo/food/bacon.txt

    hadoop fs -lsr /user/demo/food

    The put command doesn’t return a result, the list command returns one row per file or subdirectory/file:

    -rw-r–r–   1 cgross supergroup        124 2013-02-05 22:41 /user/demo/food/bacon.txt

    Enter the Hive CLI (command line interface):

    hive

    Tell Hive to show the column names above the results (all Hive commands require a semi-colon as a terminator, no result is returned from this set command):

    Set hive.cli.print.header=true;

    Create an INTERNAL table in Hive and point it to the directory with the bacon.txt file:

    CREATE INTERNAL TABLE internal1 (col1 string) LOCATION ‘/user/demo/food’;

    Oops… that failed because INTERNAL isn’t a keyword, the absence of EXTERNAL makes it a managed, or internal, table.

    FAILED: Parse Error: line 1:7 Failed to recognize predicate ‘INTERNAL’.

    So let’s create it without the invalid INTERNAL keyword. Normally we would let an INTERNAL table default to the default location of /hive/warehouse but it is possible to specify a particular directory:

    CREATE TABLE internal1 (col1 string) LOCATION ‘/user/demo/food’;

    That will return the time taken but no other result. Now let’s look at the schema that was created:. Note that the table type is MANAGED_TABLE.

    DESCRIBE FORMATTED internal1;

    col_name        data_type       comment
    # col_name              data_type               comment

    col1                    string                  None

    # Detailed Table Information
    Database:               default
    Owner:                  cgross
    CreateTime:             Tue Feb 05 22:45:57 PST 2013
    LastAccessTime:         UNKNOWN
    Protect Mode:           None
    Retention:              0
    Location:               hdfs://localhost:8020/user/demo/food
    Table Type:             MANAGED_TABLE
    Table Parameters:
    transient_lastDdlTime   1360133157

    # Storage Information
    SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    InputFormat:            org.apache.hadoop.mapred.TextInputFormat
    OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
    Compressed:             No
    Num Buckets:            -1
    Bucket Columns:         []
    Sort Columns:           []
    Storage Desc Params:
    serialization.format    1

    And now look at some rows:

    SELECT * FROM internal1;

    col1
    HDInsight_Bacon
    SQL_Bacon
    PASS_bacon
    Summit_BACON
    Database_Bacon
    NoSQL_Bacon
    BigData_Bacon
    Hadoop_Bacon
    Hive_Bacon

    What happens if we don’t specify a directory for an INTERNAL table?

    CREATE TABLE internaldefault (col1 string);

    It is created in the default Hive directory, which by default is in /hive/warehouse (dfs shells back out to Hadoop fs):

    dfs -lsr /hive/warehouse;

    We can see that Hive has created a subdirectory with the same name as the table. If we were to load data into the table Hive would put it in this directory:
    drwxr-xr-x   – cgross supergroup          0 2013-02-05 22:52 /hive/warehouse/internaldefault

    However, we won’t use this table for the rest of the demo so let’s drop it to avoid confusion. The drop also removes the subdirectory.

    DROP TABLE internaldefault;

    dfs -lsr /hive/warehouse;

    Once we dropped the internaldefault table the directory that Hive created was automatically cleaned up. Now let’s add a 2nd file to the first internal table and check that it exists:

    dfs -put c:databacon2.txt /user/demo/food/bacon2.txt;

    dfs -lsr /user/demo/food;

    -rw-r–r–   1 cgross supergroup        124 2013-02-05 23:04 /user/demo/food/bacon.txt
    -rw-r–r–   1 cgross supergroup         31 2013-02-05 23:03 /user/demo/food/bacon2.txt

    Since the CREATE TABLE statement points to a directory rather than a single file any new files added to the directory are immediately visible (remember that the column name col1 is only showing up because we enabled showing headers in the output – there is no row value of col1 in the data as headers are not generally included in Hadoop data):

    SELECT * FROM internal1;

    col1
    HDInsight_Bacon
    SQL_Bacon
    PASS_bacon
    Summit_BACON
    Database_Bacon
    NoSQL_Bacon
    BigData_Bacon
    Hadoop_Bacon
    Hive_Bacon
    More_BaCoN
    AndEvenMore_bAcOn

    Now let’s create an EXTERNAL table that points to the same directory and look at the schema:

    CREATE EXTERNAL TABLE external1 (colE1 string) LOCATION ‘/user/demo/food’;

    DESCRIBE FORMATTED external1;

    col_name        data_type       comment
    # col_name              data_type               comment

    cole1                   string                  None

    # Detailed Table Information
    Database:               default
    Owner:                  cgross
    CreateTime:             Tue Feb 05 23:07:12 PST 2013
    LastAccessTime:         UNKNOWN
    Protect Mode:           None
    Retention:              0
    Location:               hdfs://localhost:8020/user/demo/food
    Table Type:             EXTERNAL_TABLE

    Table Parameters:
    EXTERNAL                TRUE
    transient_lastDdlTime   1360134432

    # Storage Information
    SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    InputFormat:            org.apache.hadoop.mapred.TextInputFormat
    OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
    Compressed:             No
    Num Buckets:            -1
    Bucket Columns:         []
    Sort Columns:           []
    Storage Desc Params:
    serialization.format    1

    This time the table type is EXTERNAL_TABLE. You can see that the location was expanded to include the default settings which in this case are the localhost machine using the default HDFS (as opposed to ASV or Azure Storage Vault).

    Now look at the data:

    SELECT * FROM external1;

    The result set is a combination of the two bacon files:

    HDInsight_Bacon
    SQL_Bacon
    PASS_bacon
    Summit_BACON
    Database_Bacon
    NoSQL_Bacon
    BigData_Bacon
    Hadoop_Bacon
    Hive_Bacon
    More_BaCoN
    AndEvenMore_bAcOn

    That table returns the same data as the first table – we have two tables pointing at the same data set! We can add another one if we want:

    CREATE EXTERNAL TABLE external2 (colE2 string) LOCATION ‘/user/demo/food’;

    DESCRIBE FORMATTED external2;

    SELECT * FROM external2;

    You may create multiple tables for the same data set if you are experimenting with various structures/schemas.

    Add another data file to the same directory and see how it’s visible to all the tables that point to that directory:

    dfs -put c:dataveggies.txt /user/demo/food/veggies.txt;

    SELECT * FROM internal1;

    SELECT * FROM external1;

    SELECT * FROM external2;

    Each table will return the same results:

    HDInsight_Bacon
    SQL_Bacon
    PASS_bacon
    Summit_BACON
    Database_Bacon
    NoSQL_Bacon
    BigData_Bacon
    Hadoop_Bacon
    Hive_Bacon
    More_BaCoN
    AndEvenMore_bAcOn
    SQL_Apple
    NoSQL_Pear
    SQLFamily_Kiwi
    Summit_Mango
    HDInsight_Watermelon
    SQLSat_Strawberries
    Raspberrylimelemonorangecherryblueberry 123 456

    Now drop the INTERNAL table and then look at the data from the EXTERNAL tables which now return only the column name:

    DROP TABLE internal1;

    SELECT * FROM external1;

    SELECT * FROM external2;

    dfs -lsr /user/demo/food;

    Result: lsr: Cannot access /user/demo/food: No such file or directory.

    Because the INTERNAL (managed) table is under Hive’s control, when the INTERNAL table was dropped it removed the underlying data. The other tables that point to that same data now return no rows even though they still exist!

    Clean up the demo tables and directory:

    DROP TABLE external1;

    DROP TABLE external2;

    exit;

    This should give you a very introductory level understanding of some of the key differences between INTERNAL and EXTERNAL Hive tables. If you want full control of the data loading and management process, use the EXTERNAL keyword when you create the table.

    I hope you’ve enjoyed this small bite of big data! Look for more blog posts soon on the samples and other activities.

    Note: the CTP and TAP programs are available for a limited time. Details of the usage and the availability of the TAP and CTP builds may change rapidly.

    bacon.zip