Learning About The Bitnami System Database

Database Code Lines

The Elusive Bitnami System Database

If you want to spin up a fast API solution, DreamFactory is a great way to do that with a Bitnami install. Within minutes you can have a fully documented and secure REST API to utilize. Just like any program bundle, there are lots of features to learn and interact with.  Outside of a Docker Swarm or AWS ELB setup, it is pretty hard to find a way to spin up a DreamFactory instance faster. We are going to dive in a bit further to find out how to interact with the system database. Continue reading “Learning About The Bitnami System Database”

AWS Redshift – SQL Functionality on Planet-Scale Hardware

The Problem

Your manager’s peers have been bragging a lot lately about their data warehouses, analytics, and charts, and now a steady stream of data-related questions are being sent your way.  Your department maintains several databases, and the data they contain has the potential to answer everything management is asking for. But the databases are needed for day-to-day operations, and can’t scale to answer these often highly specific questions such as, “How many asparaguses were consumed by men named Fonzie in Cleveland on Tuesdays in 2013?”. How to unlock the potential of this data?

You’ve probably heard of data warehouses, which are tailor-made for this sort of witchcraft. They make it possible to unlock every bit of value from data, and find answers wickedly fast. In the past, creating and maintaining data warehouses meant large, ongoing investments in hardware, software, and people to run them. This would be a hard sell – isn’t the company already spending enough?! Good news, however! In this day of cloud computing, it’s incredibly simple to create, load, and query data warehouses. They typically charge on a usage basis, meaning you don’t need the initial upfront capital investment to get off the ground. And they are super fast – far more powerful than anything you could run in-house.

This post will focus on Amazon Web Services Redshift (Amazon Web Services = AWS). And as a bonus, I’ll demonstrate the incredible Dreamfactory, which automatically builds a slick REST API interface over the top. From there, you’re a GUI away from giving management everything they could ask for, and wowing them with extras they hadn’t even thought of. They can now stand tall amongst their fellow executives, knowing you have their back.

AWS Redshift

AWS Redshift is built upon PostgreSQL, but has been dramatically enhanced to run at “cloud scale” within AWS. There are a few ingredients to this secret sauce:

Column-oriented storage

While you don’t need a deep understanding of what’s happening under the hood to use it, Redshift employs a fascinating approach to achieve it’s mind-boggling performance. Let’s say you have data that looks like the following:
ID NAME CREATED DESCRIPTION AMOUNT

1 Harold 2018/01/01 Membership 10.00

2 Susan 2017/11/15 Penalty 5.00

3 Thomas 2016/10/01 Membership 8.00
Most SQL databases you’ve probably used in the past are row-based, which means they store their data something like this:
1:Harold,2018/01/01,Membership,10.00;

2:Susan,2017/11/15,Penalty,5.00;

3:Thomas,2016/10/01,Membership,8.00;
This is the efficient way to maximize storage, and works well for retrieving data in the “traditional fashion” (rows at a time). But when you want to slice and dice this data, it doesn’t scale very well. If you’ve got large (business-scale) volumes of data, and a variety of ways you want to query it, you can really start to strain your database. Column-based databases, on the other hand, flip this idea on its head, and store the information in a column-based format, with the *data* serving as the *key*. So the above might look something like this:
Harold:1;Susan:2;Thomas:3;

2018/01/01:1;2017/11/15:2;2016/10/01:3;

Membership:1,3;Penalty:2;

10.00:1;5.00:2;8.00:3;
This drastically improves query performance. For example, when searching for “DESCRIPTION == ‘Membership'”, the query only needs to make one database call (“give me the items with a ‘DESCRIPTION’ of ‘Membership'”), instead of inspecting each row individually (as it would have to do in a traditional, row-based database). Very cool, very fast!

Massive Parallelization

When I picture what the AWS cloud must look like, I usually conjure something up from the Matrix (except it’s full of regular computers, rather than, well, humans). Or maybe Star Trek’s “Borg”, a ridiculous planet-cube flying through space, sucking up other civilizations. I guess both of those images are a little disturbing. A safer mental image is this – data centers spanning the globe, loaded with racks and racks of computers, all connected and working together. For most computing tasks, throwing more hardware at the problem doesn’t automatically increase performance. There are bottlenecks that remain in place no matter how many processors are churning away. In our “traditional database” example, this bottleneck is typically disk I/O – the processors are all trying to grab data from the same place. To overcome this, the architecture and storage have to be arranged in a way that can benefit from parallelization. Which is exactly the case with AWS Redshift. Due to the column-based design described above, Redshift is able to take full advantage of adding processors, and it’s almost linearly scalable. This means if you double the number of computers (“nodes”, in Redshift-speak), the performance doubles. And so on. Combine this scalability with the ridiculous number of computers AWS has at it’s disposal (specifically, several Borgs-worth), and it’s like staring out at a starry night. It goes on forever in all directions.

How this works for you

If you’re sold on the power of AWS Redshift, then you’ll be pleased to learn that setup is incredibly simple. AWS documentation is top notch, a crucial thing in this brave new world. When writing this post, I followed their tutorial, and it all went smoothly. Probably took me 15 minutes, and I had the example up and running. If you already have SQL expertise, you won’t have any problem picking up Redshift syntax. There are some differences and nuances, but the standard “things” (joins, where clauses, etc) all work as expected. I typically use Microsoft’s SQL Server Management Studio (SSMS), and was able to connect to Redshift with no problem (after setting it up as a linked server). Your favorite SQL client will presumably work here as well (anything that supports JDBC or ODBC drivers). Once you get your feet wet, there are myriad tools that will load your business data into Redshift. If you’ve got SQL chops in house, I’d start with the AWS documentation, and go from there. If you need a little (or a lot) of help, a whole ecosystem of companies and tools have sprung up around Redshift. A quick Google search will introduce you to them. When you’re up and running, and growing more comfortable demanding more from the system, AWS makes it incredibly simple to add capacity. Thanks to the brilliant Redshift architecture, you just add nodes, and AWS takes care of the rest. Their billing dashboard will show you what it’s costing in real time, with no hidden or creeping costs of data centers, hardware upgrades, things going bump in the night, etc. So much magic happening under the covers, and you get the credit. The joys of cloud computing!

My Humble Example

When writing this, I used the example AWS provides (it consists of a few tables containing some fake Sales data). With everything in place, I can query from SSMS (with a little bit of “linked server” glue syntax):
exec ('-- Find total sales on a given calendar date.

SELECT sum(qtysold)

FROM sales, date

WHERE sales.dateid = date.dateid

AND caldate = ''2008-01-05'';') at redshift

sum

--------------------

210

(1 row affected)
I get a thrill when a chain of systems, architectures, and networks all flow together nicely. Somewhere in a behemoth of a data center, a processor heard my cry, and spun out this result in response. Amazing.

DreamFactory

Now that the company has access to the data, and can gleefully ask any question, they are going to want the dashboards and pretty graphs. Typically you’d use a REST API to feed the data to some sort of UI, but how to do this with Redshift? While management is tickled with their new toy, they will cloud over with suspicion if you now propose a months-long project to make it shinier. In keeping with the theme of “easy, automatic, and powerful”, I’d propose using DreamFactory. In a matter of minutes (literally), it will connect to a data store (both SQL or NoSQL), intelligently parse all the schema, and spin up a REST API layer for doing all the things (complete with attractive documentation). What used to take a team of developers months can now happen in an afternoon! Here are some screenshots of my REST API, completely auto generated from the Redshift example above. It took me about 15 minutes (12 of those spent poking around the documentation) to get this done. For my simple example, I followed their Docker instructions, and in no time was playing with the REST API depicted below:
let’s get our rest on!
 
what pretty documentation you have!
  Powerful stuff!

To Infinity and Beyond!

Now that you’ve witnessed how easily you can warehouse all your data, and bootstrap it into a REST API, it’s time to bring this to your organization. Play with it a little, get comfortable with the tools, then turn up the dials.   Want to learn more about how DreamFactory and Redshift can work together (or how to put a REST API on any database)?  Schedule a demo with us. The next time management comes calling for data, you can give it to them with a fire hose!

SQL DB REST APIs in Minutes, not Months

Have you got SQL data that you need to access from your mobile, web or IOT apps?

If so, DreamFactory provides an easy and secure way to add a REST API to any SQL database in minutes, and supports 18 popular databases, among them MS SQL Server, Oracle, MySQL, IBM DB2, Postgres, SAP SQL Anywhere, SAP Hana, MemSQL and MongoDB! All you have to do is use the [DreamFactory][2] REST API backend to connect your database, then use it to auto-generated a REST API for your database – it’s that simple!

In this blog post we’ll show how to REST-enable any SQL database, which is free forever for the databases and other services covered by our open source software. Then we’ll show some simple examples of how to use the REST API to manage your SQL schema and data.

Do you need to create a REST API for MS SQL Server, Oracle, MongoDB, or any other database? Using DreamFactory, you can be up and running in minutes rather than months! Request a demo with one of our engineers and we’ll be happy to show you how it’s done for your particular use case! If you’re a video kind of person, we have some screencasts available. If you haven’t already checked out our free open source software, you can download it here

Continue reading “SQL DB REST APIs in Minutes, not Months”

The Reusable REST API Platform Strategy

The engineering team at DreamFactory designed and built some of the very first applications that use web services. Over the years, we learned many lessons trying to create the perfect mobile backend for these applications. This article lays out some of the problems companies encounter when they build custom REST APIs from scratch, and after that, we look at some of the architectural advantages and technical characteristics of a reusable REST API platform. The REST API Complexity Problem When a company decides to start a new mobile project, the IT group first defines the business requirements, and then starts writing the actual software. Usually there is a client-side team that designs the application and a server-side team that builds the backend infrastructure. These two teams must work together to develop a REST API that connects the backend data sources to the client application. One of the most time-consuming and expensive aspects of the development process is the “interface negotiation” that occurs between these two groups. With the acceptance of BYOD and general proliferation of mobile devices, the modern enterprise may need hundreds or even thousands of mobile applications. New mobile projects typically have new requirements that were not anticipated by the existing services. You could go back and try to expand the scope of the old services, but they are already in production, and so in many situations a new REST API is created for each new project. And so the API building process continues over time with various developers, consultants, and contractors. The REST APIs are often written with different tools and developer frameworks. They run on different servers or in the cloud. They are tied to different databases and file storage systems. Each new service has different security mechanisms, credential strategies, user management systems, and API parameter names, as this diagram illustrates. Continue reading “The Reusable REST API Platform Strategy”

DreamFactory hackathon with Verizon Cloud and Mapquest

DreamFactory partnered with Verizon and Mapquest to host an all day hackathon at the beautiful Verizon campus in Dallas,Texas filled with fun, food, drinks and prizes. The September hackathon hosted 76 developers coming from different career, education and programming backgrounds which fueled creativity, collaboration and ultimately the innovation that followed. Continue reading “DreamFactory hackathon with Verizon Cloud and Mapquest”

PHP 7 doubles performance for DreamFactory

speed-increase.png DreamFactory is a high performance request/response engine for REST API Services. You can hook up any SQL or NoSQL database and instantly get a comprehensive REST API for that data source. A big part of our platform is the support for JSON Web Tokens. JWT is an open standard for representing session information that is transferred between the client and server. JWT allows DreamFactory to run in a completely stateless manner, which makes the platform very easy to scale vertically with additional server capabilities or horizontally with multiple instances between a load balancer. Continue reading “PHP 7 doubles performance for DreamFactory”

How to integrate the Twilio API with DreamFactory to enable SMS for your app

twilio.png Twilio has a superb API for integrating SMS messages into your applications. It’s easy to add Twilio as a remote HTTP service to any application you’re building with DreamFactory. DreamFactory lets you securely store your Twilio authentication credentials, call the Twilio API directly from a DreamFactory session, and easily add role-based access control to any Twilio API endpoint. This brief tutorial shows you how to add Twilio to DreamFactory in five minutes.

Continue reading “How to integrate the Twilio API with DreamFactory to enable SMS for your app”

New DreamFactory cache service supports Redis, memcached, and local storage

redis-share.png One of the most powerful features of DreamFactory is the ability to write server-side script in pre- and post-process events and use as a custom scripting service. This provides the ultimate flexibility and allows the platform to support all sorts of complex backend business logics. However, DreamFactory server-side scripts are stateless and their scope is gone once the original process is completed. Continue reading “New DreamFactory cache service supports Redis, memcached, and local storage”